PostgreSQL - Fixing Sequences

Tested on version 8.4

-- DROP FUNCTION public.SeqFix();
CREATE OR REPLACE  Function public.SeqFix() Returns void AS $$
DECLARE LIST record; MaxIDValue INTEGER; CurrentValue iNTEGER;
BEGIN
    FOR LIST iN Select table_schema, table_name, column_name, split_part(column_default,'''' ,2) AS seqname FROM information_schema.columns
                        Where table_catalog=current_database() AND column_default iS NOT NULL AND Position('nextval' iN column_default) =1 order by 1,2,3
    LOOP
        EXECUTE 'SELECT MAX(' || LIST.column_name || ') FROM ' || LIST.table_schema || '.' || LIST.table_name
        iNTO MaxIDValue;
        EXECUTE 'SELECT COUNT(*) FROM information_schema.sequences WHERE sequence_catalog=current_database() AND sequence_schema='''||LIST.table_schema||'''
             AND sequence_name='''||split_part(LIST.seqname, '.',2)||''''
        INTO CurrentValue;
        IF CurrentValue = 0 THEN
            RAISE WARNING E'?? SEQ ::\t%\t :: does not exists  ??', LIST.seqname ;
        ELSE
            EXECUTE 'SELECT last_value FROM ' || LIST.seqname
            INTO CurrentValue;
            IF CurrentValue < MaxIDValue THEN
                RAISE WARNING E'!! SEQ :: \t% = %\t<\tMAX(%.%.% = %) ', LIST.seqname, CurrentValue, LIST.table_schema, LIST.table_name, LIST.column_name, MaxIDValue;
               -- PERFORM pg_catalog.setval(LIST.seqname, MaxIDValue+1, false);
            END IF;
        END IF;
    END loop;
END;
$$ LANGUAGE plpgsql;

SELECT public.SeqFix();

Manual PostgreSQL instalation on Windows

This post is for those unlucky ppl who for some dumb reason had to install Postgres on Windows, and had no luck with it. Most common problems I came across are :
1. installation finishes but database isn't initialized - error says that libintl-8.dll is missing
2. installation stops at the beginning - VisualC Redist Setup crashes - most common on Win7

Other reasons to install PG by hand is that even when using installer with command line options, you can't get the result you wanted, like database encoding, service user etc.

What will need is :
1. Postgres binaries
2. Ntrights.exe

Let's copy all files from postgres zip to c:\pgsql.
To create service-user - in cmd as admin

net user pgsql S0m3Pa5sW0rd /add

Now to properly configure service-user

ntrights.exe -u pgsql +r SeServiceLogonRight
ntrights.exe -u pgsql -r SeInteractiveLogonRight
wmic.exe USERACCOUNT WHERE "name='pgsql'" SET PasswordExpires=FALSE

Service-user needs full control over c:\pgsql

cacls "c:\pgsql" /T /E /G "pgsql":F

To properly initialize database we need to run cmd as service-user pgsql. Still as admin run

runas /user:pgsql cmd
typ password when asked S0m3Pa5sW0rd

Now in new cmd window

cd c:\pgsql
mkdir data
cd bin
initdb.exe -D ../data -E LATIN2 --locale="Czech, Czech Republic"
exit

Now back in admins cmd

cd c:\pgsql\bin
pg_ctl.exe register -N PG84 -U pgsql -P S0m3Pa5sW0rd -D "c:\pgsql\data" -w

On Windows Vista and newer you need to uncomment last line in c:\pgsql\data\pg_hba.conf, since those versions have ipv6 support turned on by default - to test if your system qualifies

ping ::1
echo %ERRORLEVEL%

if echo returns 0, change last line in pg_hba.conf like so (remove # at the beginning of the line)

host    all     all     ::1/128      trust

To start the server - run services.msc , find and start PG84.