===== PostgreSQL =====

====== PostgreSQL database initialization ======

Create a user for example : postgres

Connect to your system a this user (su - postgres)

<cli prompt='#'>
root# mkdir /usr/local/pgsql/data
root# chown postgres /usr/local/pgsql/data
root# su postgres
postgres$ initdb -D /usr/local/pgsql/data
</cli>

Alternatively, you can run initdb via the pg_ctl program like so:
<cli prompt='#'>
$ pg_ctl -D /usr/local/pgsql/data initdb
</cli>

**Tip:** As an alternative to the -D option, you can set the environment variable PGDATA.

===== List DB and tables =====

<cli>
[root@srv1] /var/lib/pgsql/9.2/data # su postgres
[postgres@srv1]() ~/9.2/data # psql
psql (9.2.14)
Type "help" for help.

postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 ibmsc     | ibmsc    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# select datname from pg_database;
  datname
-----------
 template1
 template0
 postgres
 ibmsc
(4 rows)

postgres=# SHOW TABLES
postgres-# \connect ibmsc
You are now connected to database "ibmsc" as user "postgres".
ibmsc-# SHOW TABLES
ibmsc-# \dt
                               List of relations
 Schema |                         Name                          | Type  | Owner
--------+-------------------------------------------------------+-------+-------
 public | abstraction_applications                              | table | ibmsc
 public | abstraction_applications_service                      | table | ibmsc
 public | abstraction_capability                                | table | ibmsc
 public | abstraction_capabilityconstraint                      | table | ibmsc
 public | abstraction_capabilityconstraint_capability_values    | table | ibmsc
 public | abstraction_capabilityvalue                           | table | ibmsc
...
</cli>

===== Backup script =====

<cli prompt='#'>
root@jir01 ~]# cat /var/atlassian/backups/postgresql_dumpall.sh
</cli>
<code>
#!/bin/sh
#

DB_LIST="postgres jiradb stashdb confluencedb"

DB_USER="postgres"
DB_BACKDIR="/sqlbackup"


RC=0


for DB_NAME in $DB_LIST
do

        DB_BACKFILE="$DB_BACKDIR/$DB_NAME.dump"

        #
        #       Delete all backup
        #

        [ -r "$DB_BACKFILE" ] && rm $DB_BACKFILE
        [ -r "$DB_BACKFILE.gz" ] && rm $DB_BACKFILE.gz

        #
        #       Take new backup
        #

        su $DB_USER -c "pg_dump $DB_NAME | gzip > $DB_BACKFILE.gz"

        RC=`expr $RC + $?`

done

exit $RC
</code>