≡ Menu

postgresql databases

Download of the day: PostgreSQL 8.3 Database Server

PostgreSQL 8.3 has been released and available for download. PostgreSQL considered as the world’s most advanced open source database. From the announcement page:

This release includes a record number of new and improved features which will greatly enhance PostgreSQL for application designers, database administrators, and users, with more than 280 patches by dozens of PostgreSQL contributors from 18 countries. Version 8.3 provides greater consistency of performance than previous versions, ensuring that every user can depend on the same high performance demonstrated in recent benchmarks for every transaction, whether in peak hours or not, seven days a week, 52 weeks per year. Major performance enhancements include:

-> Heap Only Tuples (HOT), which eliminate up to 3/4 of the maintenance overhead of frequently updated tables
-> Spread checkpoints and background writer autotuning, which reduce the impact of checkpoints on response times
-> Asynchronous commit option for much faster response times on some transactions

These changes also significantly accelerate transaction processing throughput, between 5% and 30%, depending on the workload.

Download PostgreSQL 8.3 Database Server

=> Visit official project web site to grab PostgreSQL 8.3 Database Server

You may find following document interesting while using PostgreSQL:

Sysadmin because even developers need heroes!!!

Howto Backup PostgreSQL Databases Server With pg_dump command

Generally, I like to work with MySQL but some time my work force me to work with PostgreSQL database server.

Recently I had received a request to backup PostgreSQL databases as one of our client want to format and reinstall RHEL server.

PostgreSQL is a one of the robust, open source database server. Like MySQL database server, it provides utilities for creating a backup.

Step # 1: Login as a pgsql user

Type the following command:
$ su - pgsql
Get list of database(s) to backup:
$ psql -l

Step # 2: Make a backup using pg_dump

Backup database using pg_dump command. pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time. General syntax:
pg_dump databasename > outputfile

Task: dump a payroll database

Type the following command
$ pg_dump payroll > payroll.dump.outTo restore a payroll database:
$ psql -d payroll -f payroll.dump.outOR$ createdb payroll
$ psql payroll
However, in real life you need to compress database:$ pg_dump payroll | gzip -c > payroll.dump.out.gzTo restore database use the following command:$ gunzip payroll.dump.out.gz
$ psql -d payroll -f payroll.dump.out
Here is a shell script for same task:

[ ! $DIR ] && mkdir -p $DIR || :
LIST=$(psql -l | awk '{ print $1}' | grep -vE '^-|^List|^Name|template[0|1]')
for d in $LIST
  pg_dump $d | gzip -c >  $DIR/$d.out.gz

Another option is use to pg_dumpall command. As a name suggest it dumps (backs up) each database, and preserves cluster-wide data such as users and groups. You can use it as follows:$ pg_dumpall > all.dbs.outOR$ pg_dumpall | gzip -c > all.dbs.out.gzTo restore backup use the following command:
$ psql -f all.dbs.out postgresReferences: