Saturday, 16 November 2013

Backup my PostgreSQL Database

There are three fundamentally different approaches to backing up PostgreSQL databases:

1. SQL dump
2. File system level backup
3. Continuous Archiving

1. SQL dump:
•  Generate a text file with SQL commands
•  PostgreSQL provides the utility program pg_dump for this purpose.

For more details please go through the below link:-

Let us see some examples here:

 --> Take the dump of whole cluster(use pg_dumpall for that).
     ---> Go to your binaries location and execute below command:

     -bash-4.1$ ./pg_dumpall -p 5433 > /tmp/dumpall.sql

      Where -p is port of cluster.

--> Take the dump of a database(use pg_dump for that)
   plain format:
  -bash-4.1$ ./pg_dump -p 5433 -U postgres postgres >> /tmp/dump_postgres_db.sql
   Compressed Format:
   -bash-4.1$ ./pg_dump -p 5433 -U postgres postgres -Fc -f /tmp/dump.dmp

   Where -p - port
         -U - user
         -Fc - Format Compressed(you can use tar by using -Ft)
         -f - dumpfile.

--> Take the dump of a table/sequence

     -bash-4.1$ ./pg_dump -p 5433 -U postgres -t test postgres >> /tmp/dump_test_table.sql

     Where -t - tablename/sequencename

2. File system level backup:
•  An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database.
•  You can use whatever method you prefer for doing usual file system backups, for example:
   tar -cf backup.tar <location of data directory>
•  The database server must be shut down in order to get a usable backup.
•  File system backups only work for complete backup and restoration of an entire database cluster.
•  File system snapshots work for live servers.

If you take File system level backup, your database has no need to run on archivelog mode but nees to shutdown the server while taking the backup. It is a consistence backup. But you have chances to loose the data.

If you have taken the backup at 8p.m and your database is crashed due to some reason at 9p.m. Then you will loose the data of 8p.m to 9p.m as we have backup at 8p.m only. The 1 hour data will not be recovarable.

3. Continuous Archiving:
•  PostgreSQL maintains WAL files for all transactions in pg_xlog directory
•  PostgreSQL automatically maintains the WAL logs which are full and switched
•  Continuous archiving can be setup to keep a copy of switched WAL Logs which can be later used for recovery.
•  It also enables online file system backup of a database cluster.
•  Requirements:
   wal_level must be set to archive
   archive_mode must be set to on
   archive_command must be set in postgresql.conf

For more details on how to enable archiving, you can follow the below link:-

Benefits of running database in Continuous Archiving:
a. No need to shutdown the server(Means no downtime required).
b. You will not loose the data.
c. Running in archivelog mode does copy your all WAL files to the location mentioned in configuration file.
d. You can have multiple locations(duplexing of archives) for your archivelog files.(recommended to have these locations on different mount points because if one mount point got corrupted then you can get them from other one).

If you have taken the backup at 8p.m and your database is crashed due to some reason at 9p.m, then you can recover the data till 9PM as we will have copied archive files from 8p.m to 9p.m in archive log location mentioned in the configuration file. If you recover backup of 8p.m , it will automatically apply these archivelogs while database recovery, no manual operation is required.

Getting Started with PostgreSQL for Beginners

I hope these commands will hep beginners to start with PostgreSQL.

We have “pg_ctl” command which we can use to control the postgresql database. We can see this commands behavior as below.
>> How to check the postgresql service status.
$ su – root
# service postgresql­9.2 status
#su - postgres
$ /opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data status # We need to pass data directory path after ­D.

>> How to start the postgresql service.

 $ su – root
# service postgresql­9.2 start

>> How to start the postgresql as a postgres user(operating system user).

 $ /opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data start. 

>> How to stop the postgresql.
$ su – root
# service postgresql­9.2 stop
#su - postgres
$ /opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data stop ­mf 

>> How to reload the postgresql.

Reload postgresql means, force the postgresql to allow the modifications which we do in postgresql.conf/pg_hba.conf.
 $ su – root
# service postgresql­9.2 reload 

$/opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data reload 
**Note: Any modifications in pg_hba.conf must require a reload operation of a postgresql database. But, all the parameters in postgresql.conf don’t imply on the database until and unless we restart the database.
>> How to find which parameter change require restart.
This query list out all the parameters which require your postgresql database restart.
 postgres=# select name from pg_settings where context ~ 'postmaster';
This query list out all the parameters which do not require postgresql database restart but only reload.
 postgres=# select name from pg_settings where context != 'postmaster';
>> How to monitor the database connections like which application user executing which query from which ip. This query list out all the connection details of a cluster.
 postgres=# select * from pg_stat_activity ;
This query list out all the active connections list, i.e, the connections which are doing some actions on the database.
 postgres=# select * from pg_stat_activity where waiting is false;
This query list out all the connections list which are waiting.
 postgres=# select * from pg_stat_activity where waiting is true;
>> How to get the object details.  In postgresql, we have some meta commands which helps you to get the list of objects. Ex:-
 postgres=# \dt ­­> Meta command to get the list of tables.
List of relations

Schema | Name | Type | Owner
public | test_p | table | postgres 

\dv --> Meta command to get the list of views.
\di --> List of indexes.

\d  --> Table description 
You can find all these meta commands by executing “\?” in the psql terminal. These commands are not supported in any other applications. For this, we need to use “psql” terminal as below.
 bash­4.1$ ./psql ­p 5432 ­U postgres postgres
psql.bin (9.1.6, server 9.2.0)

WARNING: psql.bin version 9.1, server version 9.2.
Some psql features might not work.
Type "help" for help.

where p - port, U - user
postgres is a database in the cluster.
>> How to get the current locks in database.

 postgres=# select * from pg_locks;
>> How to check the details of replication between the primary and slave.
 postgres=# select * from pg_stat_replication ;
The above query gives us the state of the replication and lag between the primary and slave servers.
>> How to find the blocked queries.
This query list out all the sessions those are currently waiting on which sessions.
SELECT now()::timestamp,waiting.locktype AS waiting_locktype,waiting.relation::regclass::VARCHAR  ASwaiting_table,waiting_stm.Datname as WDatabase,waiting_stm.current_query AS waiting_query,(extract(epochfrom now()) - extract(epoch from  waiting_stm.query_start))::VARCHAR AS Waiting_Stmt_Total_Time,waiting.mode AS waiting_mode, AS waiting_pid,other.locktype::VARCHAR AS other_locktype,other.relation::regclass::VARCHAR AS other_table,other_stm.Datname::VARCHAR as ODatabase,other_stm.current_query AS other_query,(extract(epoch from now())­-extract(epoch from other_stm.query_start))::VARCHAR AS Other_Stmt_Total_Time,other.mode AS other_mode, AS other_pid,other.granted::VARCHAR AS other_granted FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.procpid = JOIN pg_catalog.pg_locks AS other On ((waiting.database = other.database AND waiting.relation = other.relation) OR waiting.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.procpid = WHERE NOT waiting.granted AND <>;
>> How to get the database statistics like hitratio, dml statistics.
SELECT sdb.datname,blks_read,blks_hit,round((blks_hit::float / (blks_read+blks_hit+1) * 100)::numeric, 2) as cachehitratio,xact_commit,xact_rollback,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,ROUND(100.0*AGE(DATFROZENXID)/ (SELECT SETTING::float FROM PG_SETTINGS WHERE NAME ILIKE 'autovacuum_freeze_max_age')) FROM pg_stat_database sdb INNER JOIN pg_database db on sdb.datname=db.datname WHERE sdb.datname !~ '^(template(0|1))' ORDER BY cachehitratio desc;
The above query will give you the database statistics. 

PostgreSQL Installation

PostgreSQL Installation using installer:

Here are steps to install the postgres through installer:

 --> Download the installer from below link:

 --> Goto the location of PostgreSQL 9.2 installer( and run the installer in text mode as below

 ./ --mode text   --> You need to provide the below details while installation.

Installation Directory [/opt/PostgreSQL/9.2]: ===> Provide the base directory for installation (We have provided /opt/PostgreSQL92 in test servers)

Data Directory [/opt/PostgreSQL92/data]: ===> Provide the data directory location(We have provided /opt/PostgreSQL92/data location)

Password : ==> provide the password for "postgres" superuser Retype password :

Port [5433]: ===> provide the port number

[1] [Default locale] [2] C
[3] en_AG
[4] en_AG.utf8
[5] en_AU.utf8 [6] en_BW.utf8 [7] en_CA.utf8 [8] en_DK.utf8 [9] en_GB.utf8 [10] en_HK.utf8 [11] en_IE.utf8 [12] en_IN
[13] en_IN.utf8 [14] en_NG [15] en_NG.utf8 [16] en_NZ.utf8 [17] en_PH.utf8 [18] en_SG.utf8
[19] en_US.utf8
[20] en_ZA.utf8
[21] en_ZW.utf8
[22] POSIX
Please choose an option [1] : 1 ==> select the locale (By default it is [1] Default locale)
Do you want to continue? [Y/n]: y
---------------------------------------------------------------------------- Please wait while Setup installs PostgreSQL on your computer.
0% ______________ 50% ______________ 100% 

--> After the installation, you need to set environment variables. There is a file in which all environment variables are exported in /opt/PostgreSQL92(which base installation directory). You can run that file to set env settings Or you can set it in .bash_profile where every postgres user login env file gets run.

--> Open the bash file $vi .bash_profile -- Enter the below line:

source /opt/PostgreSQL92/ -- Run the bash file for the first time
$. .bash_profile

--> Now you can connect to database by just typing the "psql"

-bash-4.1$ psql 
Timing is on.
psql.bin (9.2.5)
Type "help" for help.