PostgreSQL Commands

PostgreSQL command line entry:

$ psql

Show databases:

# \du

Show users:

# \l

Connect database:

# \connect dbname

List all tables in the current database:

# \dt

Describe a table

# \d employees

List all tables in database along with some additional information:

# \d+

Quit postgresql command line:

# \q

PostgreSQL 9.3 İnstall

# yum install yum install https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-oraclelinux93-9.3-3.noarch.rpm
# yum install postgresql93-server
# yum install oracle-rdbms*
# service postgresql-9.3 initdb
# service postgresql-9.3 start
# su - postgres
$ /usr/pgsql-9.3/bin/pg_ctl status -D /var/lib/pgsql/9.3/data
pg_ctl: server is running (PID: 10774)
/usr/pgsql-9.3/bin/postgres "-p" "5432" "-D" "/var/lib/pgsql/9.3/data"

Hot Backup

in postgres.conf file set parameters the fallowing:

# postgres.conf file
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/9.3/backups/%f && cp %p /var/lib/pgsql/9.3/backups/%f'
#archive_command = "if not exist this file in backups folder, copy redolog file to backups"

# service postgresql-9.3 restart

postgreSQL service to be restarted. Now zip the file, and set to restore_format parameter in recevery.conf

# su - postgres
$ psql
# select pg_start_backup('/var/lib/pgsql/9.3/backups/', true);
 //bu data dizinine yazmayı devre dışı bırakıyor. redo lobular devre dışı kalıyor. bu sayede tarlaya biliyoruz.
$ tar -cf backup.tar /var/lib/pgsql/9.3/data
$ psql
# SELECT pg_stop_backup();

Recovery:

// recovery.conf
restore_command = 'cp /var/lib/pgsql/9.3/backups/%f %p'
$ /usr/pgsql-9.3/bin/pg_ctl start -D /var/lib/pgsql/9.3/data -l /var/lib/pgsql/9.3/pg.log