## page was renamed from SetupPostgresDatabase

== 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