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

• 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.

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

CategorySql