Diff for "PostgresDatabase"

Installing Postgres from scratch:

install packages downloaded from postgresql web site located in /u2/programs/postgres_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

set password for system user postgres then use postgres system account for the rest of setup

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

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
pg_dump -f /backup_dir_name/dbfile_name.aaa dbname

Restore database

To restore PostgresSql database fresh 100% emty database need to be created. If old database still exist it need to be renamed or deleted

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
psql -f /backup_dir_name/dbfile_name.aaa dbname          #restore database
cp -f /backup_dir_name/*.idx /ccdev/company_name/        #restore indexes
psql -U ccc dbname
>VACUUM ANALYZE
>\q

Extended Postgres documentation available on sam:/usr/share/doc/postgresql-8.1.3/html/index.html

CategorySql