There are two different types of backup: logical backup and physical backup. They can be done hot (database cluster is running) or cold (database cluster is stopped). PostgreSQL actually supports physical cold backup and logical hot backup.

Physical cold backup

Physical cold backup means coping the files (if necessary also the rdbms binaries) to a place where you can get it back in case of problems.

Advantages: Physical cold backup is generally much faster than logical hot backup.

Disadvantages: Database cluster has to be shut down during backup. You can not use cold backup for upgrading a PostgreSQL database cluster.

Example:

pg_ctl stop $PGDATA
tar --bzip2 -cvf <pg_cold_backup_$(date '+%Y-%m-%d').tar.bz2 --dereference $PGDATA
pg_ctl start $PGDATA

Logical hot backup

Environment

Maintenance

Migration

Operation

Programming

NULL

The NULL value is NOT zero. You can see this in the following example:

SELECT * FROM test;

       NR
----------
        1
        2
     NULL

SELECT * FROM test WHERE nr < 2;

       NR
----------
        1

SELECT * FROM test WHERE nr > 1;

       NR
----------
        2

Recovery

Security

Functions to create, drop and alter database users. This functions prevent granting CREATE USER to just a little operator account because this gives SUPERUSER privileges!!! They are simulating the CREATE USER, ALTER USER and DROP USER system privileges. This script does NOT work before PostgreSQL 7.4.!!!

f_create_user.tar.gz (1.8 kb)

Tuning

Indexing

It is much faster to add an index to a full table than add rows to a table with an index.

I/O Tuning

Q: I want to try to decrease I/O wait by setting tables on one disk and indexes on another one. Is this a good idea?

A: There are less drastic ways to get more performance out of PostgreSQL. One thing you can do is move your WAL files (in pg_xlog directory) to a different disk, as they are very heavily accessed.
Caution: You should only do this, when the database cluster is down!

Example:

/<mountpoint1>/.../pg_xlog here is the location of your WAL files.
/<mountpoint2>/.../pg_xlog here you want to have it later.

pg_ctl stop $PGDATA
mkdir -p /<mountpoint2>/.../pg_xlog
mv /<mountpoint1>/.../pg_xlog/* /<mountpoint2>/.../pg_xlog/
rmdir /<mountpoint1>/.../pg_xlog
ln -s /<mountpoint2>/.../pg_xlog/ /<mountpoint1>/.../pg_xlog
pg_ctl start $PGDATA

Hint: You should secure that <mountpoint1> and <mountpoint2> are on different disks. For systematic naming see: Environment or pg_env.

Upgrade

Do ALLWAYS a physical cold backup BEFORE you are upgrading. This is only a fall back security scenario in case of troubles. This backup can NOT be used for upgrading!