PostgreSQL 9.0 Streaming Replication on Windows

A major milestone in PostgreSQL 9.0 is Streaming Replication(including DDL). Many of you all used configuring SR on Linux, but I would be presenting SR on Windows Platform. PostgreSQL wiki is the best guide for setting up the Streaming Replication.

For setting up SR on Windows, I would recommend to follow the PostgreSQL wiki steps with minor changes what needed for Windows Platform. I would like to show only the changes what you have to look for on Windows Platform in my blog.
http://wiki.postgresql.org/wiki/Streaming_Replication

Step 1. (Before configuring SR, add the port)

On primary, you need to configure the accepting port. Below link will guide for adding port.
http://support.microsoft.com/kb/842242

Note: Adding the port differ's from different Windows Platforms.

Step 2. (Before configuring SR, Create common mount point for Archives)

Create one common mount point where Primary and Standby write/read the Archives. Mount point should own the Postgres user permissions. My common mount point: '10.10.101.111'

Step 3.

On Primary, changes in PostgreSQL.conf.

wal_level = hot_standby 
archive_mode = on 
archive_command = 'copy %p  \\\\10.10.101.111\\pg\\WAL_Archive\\%f'
max_wal_senders = 5
wal_keep_segments = 32

Step 4.

On Standby,

1) Edit the postgresql.conf file and change the below parameters.
        listen_address='*'
        hot_standby = on

2) Add the primary server entry in pg_hba.conf
        host    replication   postgres  primary.IP.address/22  trust

3) Create recovery.conf
        standby_mode = 'on'
        primary_conninfo = 'host=10.10.101.111 port=5432 user=postgres'
        trigger_file = 'C:\\stopreplication\\standby.txt'
        restore_command = 'copy \\\\10.10.101.111\\pg\\WAL_Archive\\%f %p'
     
Note: Create the recovery.conf file by copying any of the .conf files from the /data_directory.

Mentioned steps are the only changes you need to take care when setting up SR on Windows, rest all follow the procedure on PostgreSQL Wiki.

Regards
Raghav

PostgreSQL 9.0 Backup & Recovery

In PostgreSQL, Backup & Recovery are very user friendly comparing with other database. Many of them won't agree to this, ok lets not get into debate. Coming to Backups, PostgreSQL does'nt support INCREMENTAL BACKUP, however there are very consistant backup tools and OS level work-around to achieve this goal.

My pictorial presentation on PostgreSQL Backup and Recovery gives a complete conceptial idea. Looking into Diagram you can make out which backups can be used to restore or recover.

Logical Backup

pg_dump,pg_restore and pg_dumpall utilities used for logical backups. pg_dump and pg_restore will help in taking backups for Database level, Schema level and Table Level. Pg_dumpall used for cluster level dump.

Three formats are supported with pg_dump, Plain SQL format, Custom Format and Tar Format. Custom and Tar Format dumps are compatible with pg_restore utility whereas Plain SQL format dumps are compatible with psql utility for restoration.

Below are the examples for each Backup levels and with related restore commands.

Note: Set the defaults for PGDATABASE, PGUSER, PGPASSWORD and PGPORT in .bash_profile(Environment Variables in Windows)

Plain SQL Format Dump and Restore

$ pg_dump -U username -Fp dbname  >  filename
or
$ pg_dump -U username dbname -f  filename
or
$ pg_dump -Fp -U username dbname -f  filename

For restoring use psql command

$ psql -U username -f filename dbname
or
postgres=# \i SQL-file-name     //in psql terminal with \i option

Custom Format

$ pg_dump -Fc dbname -f filename
$ pg_restore -Fc -U username -d dbname filename.dmp

Tar Format

$ pg_dump -Ft dbname -f filename
$ pg_restore -U username -d dbname filename
or
$ cat tar-file.tar | psql -U username dbname

Note: Schema Level and Tables Level dumps can be performed in the same way by adding related options.

Cluster Level Dump:

$pg_dumpall -p portnumber > filename

For restoring use psql command

$ psql -f filename

There are very best way of taking dumps and restoring methodolgies. In particular, Simon Riggs and Hannu Krosing - "PostgreSQL 9 Administration Cookbook - 2010" book is good way to start with PostgreSQL Backup and Recovery published by www.2ndQuadrant.com.

Physical Backup (File system Backup)

Cold Backup:

In cold backup, its a simple file system backup of /data directory when Postgres Instance is down, mean, to achieve a self-consistent data directory backup, the database server should be shut down before copying. PostgreSQL gives flexibility to keep pg_xlog and pg_tblspce in different mount points via softlink. While copying the /data directory including the soft link's data, use the below command.

tar czf backup.tar.gz $PGDATA
or
cp -r $PGDATA /backup/
or
rsync -a $PGDATA /wherever/data

Hot Backup (Online Backup):

In Hot Backup, cluster will be up and running and the Database should be in Archive Log Mode. Two system functions will notify the instance about starting and stopping the Hot Backup process(pg_start_backup(),pg_stop_backup()). Before going forward with Online Backup, let's discuss on the Database Archive Log mode which is mandatory for Online Backups.

Enabling WAL Archiving:

Coming posts of mine will brief about PITR / Tunning WAL etc., presently we look into WAL Archiving. In PostgreSQL database system, the actual database 'writes' to an addition file called write-ahead log (WAL) to disk. It contains a record of writes that made in the database system. In the case of Crash, database can be repaired/recovered from these records.

Normally, the write-ahead log logs at regular intervals (called Checkpoints) matched against the database and then deleted because it no longer is required. You can also use the WAL as a backup because,there is a record of all writes made to the database.

Concept of WAL Archiving:

The write-ahead log is composed of each 16 MB large, which are called segments. The WALs reside under pg_xlog directory and it is the subdirectory of 'data directory'. The filenames will have numerically named in ascending order by PostgreSQL Instance. To perform a backup on the basis of WAL, one needs a basic backup that is, a complete backup of the data directory, and the WAL Segments between the base backup and the current date.

Configuring the archiving of WAL segments can be chosen by setting the two configuration parameter's archive_command and archive_mode in the postgresql.conf. Making the cluster into Archive-log mode requires RESTART.

archive_mode= on/off (boolean parameter)
archive_command = 'cp –i %p / Archive/Location/ f% '

Note: % p for the file to copy with path used as a file name and % f without a directory entry for the destination file.

For further information about the Archiver Process, refer to the post PostgreSQL 9.0 Memory & Processess.

Online Backup :

To take online backup:

Step 1 : Issue pg_start_backup('lable') in the psql terminal
postgres=# select pg_start_backup('fb');
Step 2 : OS level copy the $PGDATA directory to any Backup Location
$ cp -r $PGDATA  /anylocation
Step 3 : Issue pg_stop_backup() in psql terminal.
postgres=# select pg_stop_backup();

Note: It is not necessary that these two functions should run in the same database connection. The backup mode is global and persistent.

In PostgreSQL, there is no catalog to store the Start and Stop time of the Online backup. However, when online backup is in process, couple of the files created and deleted.

pg_start_backup('label') and pg_stop_backup are the two system functions to perform the Online Backup. With pg_start_backup('label') a file backup_label is created under $PGDATA directory and with pg_stop_backup() a file 'wal-segement-number.backup' file created under $PGDATA/pg_xlog. Backup_label will give the start time and Checkpoint location of WAL Segment, it will also notify the PostgreSQL instance that Cluster is in BACKUP-MODE. 'wal-segment-number.backup' file under $PGDATA/pg_xlog directory describes the start and stop time, Checkpoint location with WAL segement number.

Note: After pg_stop_backup(), backup_label file is deleted by the PostgreSQL instance.

Do post your comments, suggestions.

--Raghav

ERROR: could not load library "/opt/PostgreSQL/9.0/lib/postgresql/plperl.so": libperl.so:

Above error thrown, when I was trying to create language plperlu. It indicates that there is a missing library libperl.so. Check out the steps performed to overcome this issue and successfully creating the plperlu language in PostgreSQL.

Method 1 (Finding the libperl.so and making softlink to that location)

postgres=# create LANGUAGE plperlu;
ERROR:  could not load library "/opt/PostgreSQL/9.0/lib/postgresql/plperl.so": libperl.so: cannot open shared object file: No such file or directory
postgres=# \q

Now, search for the file libperl.so on your box and add that location to your LD_LIBRARY PATH. Dont forget switching user to postgres.

[root@localhost /]# find -name libperl.so
./usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE/libperl.so
./usr/lib/perl5/CORE/libperl.so
./usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE

[root@localhost /]# su - postgres

Set the LD_LIBRARY_PATH

-bash-4.1$ export LD_LIBRARY_PATH=/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE:$LD_LIBRARY_PATH

Restart the cluster to effect the changes.

-bash-4.1$ pg_ctl restart
waiting for server to shut down....... done
server stopped
server starting

You are now ready to create the language.

-bash-4.1$ psql
psql (9.0.1)
Type "help" for help.

postgres=# create LANGUAGE plperlu;
CREATE LANGUAGE

Method 2 (By installing the latest ActivePerl)

Step 1:
After installing ActivePerl, set the LD_LIBRARY_PATH.

postgres@localhost:/opt/ActivePerl-5.12/lib/CORE> export LD_LIBRARY_PATH=/opt/ActivePerl-5.12/lib/CORE:/opt/PostgreSQL/9.0/lib/postgresql

Step 2:

postgres@localhost:/opt/PostgreSQL/9.0/lib/postgresql> ldd plperl.so 
       linux-vdso.so.1 =>  (0x00007fff8cf79000)
       libperl.so => /opt/ActivePerl-5.12/lib/CORE/libperl.so (0x00007fbd3d654000)
       libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fbd3d40a000)
       libdl.so.2 => /lib64/libdl.so.2 (0x00007fbd3d206000)
       libm.so.6 => /lib64/libm.so.6 (0x00007fbd3cfb0000)
       libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fbd3cd74000)
       libutil.so.1 => /lib64/libutil.so.1 (0x00007fbd3cb71000)
       libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fbd3c954000)
       libc.so.6 => /lib64/libc.so.6 (0x00007fbd3c5f5000)
       /lib64/ld-linux-x86-64.so.2 (0x00007fbd3dad8000)

Step 3:

postgres@localhost:/opt/PostgreSQL/9.0/lib/postgresql> pg_ctl restart

postgres@localhost:/opt/PostgreSQL/9.0/lib/postgresql> psql
psql (9.0.2)
Type "help" for help.

postgres=# create language plperlu;
CREATE LANGUAGE

Post your comments.

--Raghav

Size of Partition Table in PostgreSQL 9.0

In PostgreSQL, every table is an object, using pg_relation_size('object_name') will give the size of the object. If you send the partition table in the place of 'object_name', it gives only that object size but not the sizes of child tables.

Check out the example given below.

postgres=# \dt+
                          List of relations
 Schema |     Name      | Type  |  Owner   |    Size    | Description
--------+---------------+-------+----------+------------+-------------
 public | child1        | table | postgres | 8192 bytes |
 public | child2        | table | postgres | 8192 bytes |
 public | parent        | table | postgres | 0 bytes    |
(3 rows)

pg_relation_size() on parent table will not give the exact size.

postgres=# select pg_size_pretty(pg_relation_size('parent'));
 pg_size_pretty
----------------
 0 bytes
(1 row)

To achieve partition table size, firstly know the concerned child tables and its sizes. Using pg_inherits catalog table will help in getting the information of child tables with sizes and later sum them for exact size. I have tried writing a small function using pg_inherits to get it done.

CREATE OR REPLACE FUNCTION pg_partition_table_size(text) returns numeric as
$$
select sum(to_number(pg_size_pretty(pg_relation_size(inhrelid::regclass)),'999999999')) from pg_inherits where inhparent=$1::regclass;
$$ language sql;

Now, send the partition table to the function.

postgres=# select pg_partition_table_size('parent');
 pg_partition_table_size
-------------------------
                   16384
(1 row)

Is it not useful. Do post your comments, they will be greatly appreciated.

--Raghav

PostgreSQL 9.0 Memory & Processes

Going forward with PostgreSQL Architecture, here I would be discussing about the utility process and memory with informative links. Many of the commiters have already documented insightfully about the process and memory, links provided here for those. Modest presentation from my end about the PostgreSQL Utility Process.

Every PostgreSQL Instance startup, there will be a set of utilty process(including mandatory and optional process) and memory. Two mandatory process (BGWRITER and WAL Writer) and four optional process (Autovacuum launcher,stats collector,syslogger, and Archiver). You can check it out with the command 'ps -ef | grep postgres' given below in figure 10.1.

Figure 10.1

Overview of the Process and memory.

Figure 10.2

Above figure 10.2 shows the processes attached to the PostgreSQL Shared memory.

BGWriter/Writer Process:

BGWRITER or WRITER process is a mandotary process.

All PostgreSQL server process reads data from disk and moves them into Shared Buffer Pool. Shared Buffer pool uses ARC algorithm or LRU(least-recently used) mechanism to select the page it evicts from the pool. BGWRITER spends much of its time sleeping, but every time it wakes, it searches through the shared buffer pool looking for modified pages. After each search, the BGWRITER chooses some number of modified pages, writes them to disk, and evicts those pages from the shared buffer pool. BGWRITER process can be controled with three parameters BGWRITER_DELAY,BGWRITER_LRU_PERCENT and BGWRITER_LRU_MAXPAGES.

http://www.enterprisedb.com/docs/en/9.0/pg/kernel-resources.html

http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-resource.html

WAL Writer Process:

WAL writer process is a mandatory process.

WAL writer process writes and fsync WAL at convenient Intervals. WAL buffers holds the changes made to the database in the transaction logs, in order to guarantee transaction security. WAL buffers are written out to the disk at every transaction commit, as WAL writer process is responsible to write on to the disk. WAL_WRITER_DELAY parameter for invoking the WAL Writer Process, however there are other parameters which also keeps the WAL Writer busy. Follow below link.

http://www.enterprisedb.com/docs/en/8.4/pg/wal-configuration.html

Stats Collector Process:

Stats collecotr process is optional process, default is ON. 

Stats collector process will collect the information about the server activity. It count number of access to the tables and indexes in both disk-block and individual row items. It also tracks the total number of rows in each table, and information about VACUUM and ANALYZE actions for each table. Collection of statistics adds some overhead to query execution, whether to collect or not collect information. Some of the parameter in the postgresql.conf file will control the collection activity of the stats collector process. Following link will brief more about the stats collector process and its related parameters.

http://www.enterprisedb.com/docs/en/9.0/pg/monitoring-stats.html

Autovacuum Launcher Process:

Autovacuuming is a optional Process, default is ON.

For automating the execution of VACUUM and ANALYZE command, Autovacuum Launcher is a daemon process consists of multiple processes called autovacuum workers. Autovacuum launcher is a charge of starting autovacuum worker processes for all databases. Launcher will distribute the work across time, attempting to start one worker on each database for every interval, set by the parameter autovacuum_naptime. One worker will be launched for each database, set by the parameter autovacuum_max_workers. Each worker process will check each table within its database and execute VACUUM or ANALYZE as needed. Following will breif about the AUTOVACUUM LAUNCHER PROCESS parameters.

http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-autovacuum.html

Syslogger Process / Logger Process :

Figure 10.3

Logging is an optional process, default is OFF.

As per the figure 10.3, it is clearly understood that all the utility process + User backends + Postmaster Daemon attached to syslogger process for logging the information about their activities. Every process information is logged under $PGDATA/pg_log with the file .log. 

Note: If the data directory is created with INITDB command, then there wont be pg_log directory under it. Explicit creation is needed.

Debugging more on the process information will cause overhead on the Server. Minimul tunning is always recommended, however, increasing the debug level when required. Link below will brief on logging parameters. 

http://www.enterprisedb.com/docs/en/8.4/pg/runtime-config-logging.html

Archiver Process:

Figure 10.4

Achiver process is optional process, default is OFF.

Above Figure 10.4 is made from my observation on the Archiving process in PostgreSQL. Setting up the database in Archive mode means, to capture the WAL data of each segment file once it is filled, and save that data somewhere before the segment file is recycled for reuse. 

Diagrammatical explination on Numbering tags.  

1. On Database Archivelog mode, once the WAL data is filled in the WAL Segment, that filled segment named file is created under $PGDATA/pg_xlog/archive_status by the WAL Writer naming the file as ".ready". File naming will be "segment-filename.ready".

2. Archiver Process triggers on finding the files which are in ".ready" state created by the WAL Writer process. Archiver process picks the 'segment-file_number' of .ready file and copies the file from $PGDATA/pg_xlog location to its concerned Archive destination given in 'archive_command' parameter(postgresql.conf). 

3. On successful completion of copy from source to destination, archiver process renames the "segment-filename.ready" to "segment-filename.done". This completes the archiving process. 

It is understood that, if any files named "segement-filename.ready" found in $PGDATA/pg_xlog/archive_status are the pending files still to be copied to Archive destination. 

For more information on parameters and Archiving, see the below link.

http://www.enterprisedb.com/docs/en/9.0/pg/continuous-archiving.html

Please do post your comments/suggestion on this article, they would be greatly appreciated.

Regards

Raghav