Diff for "PostgresDatabase"

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

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 -U postgres -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