≡ Menu


Howto Setup PostgreSQL in the OpenSolaris OS

This article describes key features of PostgreSQL 8.2, which have been available in OpenSolaris since build 66. You know the PostgreSQL logo is an elephant: Find out why the PostgreSQL 8.2 features in OpenSolaris are huge.

PostgreSQL in the OpenSolaris OS

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:

Linux: Iptables Allow PostgreSQL server incoming request

PostgreSQL is an object relational database system that has the features of traditional commercial database systems with enhancements to be found in next-generation DBMS systems. PostgreSQL is free and the complete source code is available.

Open port 5432

By default PostgreSQLt listen on TCP port 5432. Use the following iptables rules allows incoming client request (open port 5432) for server IP address :

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

As posted earlier, you do not wish give access to everyone. For example in web hosting company or in your own development center, you need to gives access to POSTGRES database server from web server only. Following example allows POSTGRES database server access ( from Apache web server ( only:

iptables -A INPUT -p tcp -s --sport 1024:65535 -d --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s --sport 5432 -d --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Allow outgoing POSTGRES client request (made via postgresql command line client or perl/php script), from firewall host

iptables -A OUTPUT -p tcp -s --sport 1024:65535 -d 0/0 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A INPUT -p tcp -s 0/0 --sport 5432 -d --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT