Installing Postgres from scratch:
Fedora 3/4
install packages downloaded from postgresql web site located in /u2/programs/databases_and_odbc/ on sam (do not use yum install, it will install ancient garbage 5-10 years old)
rpm --install /u2/programs/postgres_and_odbc/postgresql-libs-8.1.3-1PGDG.i686.rpm rpm --install /u2/programs/postgres_and_odbc/postgresql-devel-8.1.3-1PGDG.i686.rpm rpm --install /u2/programs/postgres_and_odbc/postgresql-8.1.3-1PGDG.i686.rpm rpm --install /u2/programs/postgres_and_odbc/postgresql-server-8.1.3-1PGDG.i686.rpm
Debian 3.1
install packages gathered from nowhere 
and located in /u2/programs/databases_and_odbc/Debian on sam
dpkg -i --force-all .....deb
set password for system user postgres then use postgres system account for the rest of setup
If /var do not have enough space or not supposed to hold user data create directory somewhere and move /var/lib/pgsql/data and /var/lib/pgsql/backups there.
/etc/init.d/postgresql stop mkdir /u/Database/postgresql [root@sam ccdev]# mv /var/lib/pgsql/data /u/Database/postgresql [root@sam ccdev]# mv /var/lib/pgsql/backups /u/Database/postgresql [root@sam ccdev]# ln -s /u/Database/postgresql/backups /var/lib/pgsql/backups [root@sam ccdev]# ln -s /u/Database/postgresql/data /var/lib/pgsql/data /etc/init.d/postgresql start
create default table if does not exist, try psql without args, if complains:
createdb
set password for default database user postgres:
psql $alter user postgres password 'sql'; $\q
setup/change identification method
vi /var/lib/pgsql/data/pg_hba.conf --change ident to password (unless you have any better idea (for example --how to use ident service or use encripted passwords ;-)
restart postmaster:
/etc/init.d/postgresql stop /etc/init.d/postgresql start
Backup/Restore database
Backup database
Optional, to make sure that there are no open session and we will not backup leftover junk: bash>psql -U ccc dbname >vacuum analyze freeze #make sure that there are no open session for this company >\q cp /ccdev/company_name/*.idx /backup_dir_name/ #copy index files to dump whole database run >pg_dumpall -U username > filename to dump company run >pg_dump -U username -Fc -f filename dbname where username will be db owner or superuser, postgres by default filename - name of dump file dbname - name of database to dump (normaly company name lowercase, but can be any, can be found in /etc/odbc.ini, To dump database into sql asci text (definately not the best for restoring but can be very useful otherwise) >pg_dump -f /backup_dir_name/dbfile_name.aaa dbname
Restore database
Restore Database from ascii sql script (from pg_dump -f)
To restore PostgresSql database fresh 100% emty database need to be created. If old database still exist it need to be renamed or deleted
bash>psql #login as postgres, enter postgres password
>DROP DATABASE old_dbname #to destroy old database OR
>ALTER DATABASE old_dbname RENAME TO old_dbname_backup; #to rename old database, DO NOT FORGET to drop it when it will not be used anymore
>CREATE DATABASE dbname TEMPLATE=template0 #create fresh database from empty system TEMPLATE0
>\q
bash>psql -U postgres -f /backup_dir_name/dbfile_name.aaa dbname #restore database
bash>cp -f /backup_dir_name/*.idx /ccdev/company_name/ #restore indexes
bash>psql -U ccc dbname
>VACUUM ANALYZE
>\q
Restore Database from internal format(from pg_dump -Fc), should be STANDARD To restore whole company
>pg_restore -U username -d dbname filename
- database dbname is not necessary is existing company, dump can be restored into the different one but it should exist before If we are using existing database make sure it is empty or run it with -c option to restore single table
>pg_restore -U username -d dbname -t tablename filename
- table should be empty (-a option) or not exist at all. restoring not existent table does not make too much sense because it will not restore objects depending on it like free recno stuff, but we can rename existant table and restore original for comparision.