March 6, 2021
Hot Topics:

Introductory PostgreSQL Administration Tasks

  • By Damond Walker
  • Send Email »
  • More Articles »


There will be times that it becomes necessary to move data from one version of pgsql to another, or to another database server altogether. To do this, you use a tool named pg_dump. This program will examine a database and generate a script to rebuild the components of the database. This script contains everything except the CREATE DATABASE command required to restore the database to the same point from which you dumped it. The output of pg_dump is in ASCII, so it's possible to use the output on a totally alien system: from your typical x86 box to a SPARC-based server, as one example.

You could use pg_dump as a poor man's backup utility, but this kind of usage isn't encouraged. Pg_dump works on entire tables or databases and there are no options to specify which records are to be dumped.

Pg_dump is typically used in two cases: transferring data and upgrades.

In the case of doing a transfer, you simply specify the command line options to pg_dump and redirect the output to a file on whatever file system you like. Then, on the other system, you simply run psql with the f command line option.

In the case of upgrades, the pgsql developers will sometimes make changes to the database that will require that you dump the data from the old server, compile and install the new software, and then import the dump back into the new database installation.

Using pg_dump is straightforward. You simply specify a database name on the command redirecting output to a file or another pgsql database. To dump the contents of a database named DAMOND you would type the following command:

pg_dump damond > damond.txt
That example was simple enough--now for a more useful one. Let's say you want to transfer the contents of database, DAMOND, to DAMOND2:
        createdb damond2; pg_dump damond |psql d damond2 
You can specify table names with pg_dump. The following command will just transfer table test1 from database DAMOND to DAMOND2 using the t (or --table) command line option:
createdb damond2; pg_dump t test1 damond |psql d damond2 
You should be aware of one command line option, depending on how your developers create their applications. Every row of data in pgsql contains a hidden field named OID. An OID is a unique value assigned to every record in the database. Some developers will use the OID to locate individual records quickly using indexes. Pg_dump will not retain the OIDs in the current database when it starts to dump the data. To tell pg_dump to retain the OIDs, you must specify o (or oids) on the command line.

Pg_dump works with individual databases within pgsql. To dump all the databases on the server would require a separate pg_dump command for each one. The pgsql developers have provided a wrapper around pg_dump named pg_dumpall that will dump all the databases on a server with one command line.

Security: First Things First: Connecting to the Postmaster

When a user attempts to connect to pgsql, the first security gate they have to pass is defined by the entries in a file named pg_hba.conf. This file, located in /, tells pgsql both which clients can connect and what type of challenge or response to issue when those users attempt to make the connection.

When pgsql is installed (either via RPM or by manually building the source), the default pg_hba.conf file is set to allow trusted connections. Trusted connections, in this case, mean that pgsql will believe that you are whoever you say you are. If you have a new install of pgsql on your system, type the following at the command line:

psql U postgres d template1
User postgres, on my system, is the pgsql super user--able to create and destroy databases. If you have installed RedHat RPMs with the base, then you will have a postgres user defined, as well, in your /etc/passwd file. Otherwise, you should specify whatever user id you decided upon when you compiled and installed pgsql.

If you have an unmodified pgsql installation, you should be presented with the standard psql prompt. This isn't good. Luckily, the file itself is well documented, and the changes are easy to make. Su to the pgsql super user, load the file in an editor, and read on.

The basic line format of the pg_hba.conf file goes like this:

Name Description
TYPE The type of host you are specifying. Generally this item will be HOST or LOCAL. Another type, HOSTSSL, is discussed in the documentation.
DB The database this entry is for. You are allowed to have several entries per database.
IP The IP address of the host that is connecting to the server.
IP_MASK The IP MASK of the host connecting to the server.
AUTHENTICATION The type of authentication to be made between the client and server. There are several valid options, which are discussed below.
AUTHENTICATION_OPTIONS Any other options you specify based on the authentication type specified above.
Every time a host tries to connect to pgsql, the pg_hba.conf file is scanned. The first entry found in the file that matches the connection type (LOCAL, HOST, and HOSTSSL) and IP address of the host is used to determine what happens next. Because of this, order of entries is very important. Put your most restrictive entries for any given database first.

The best way to learn about pg_hba.conf is to see a few examples. Entries in my configuration file are as follows:

local all trust
The above entry allows anyone to connect to the databases on the system as anybody, while logged onto the machine either via telnet or if they're actually sitting at the terminal. A handy entry for development purposes, but not something you would want on a production data server. At a minimum, you'd want to use the following line to enforce standard username/password authentication:
local all password 
The next entry in my configuration file is:
host all 192.168.99. password
The above line tells pgsql to challenge anyone from my local network for a password when they try to connect to the database.

The default connection activity, assuming no other entry describes the connection attempt, is to reject all hosts. The pgsql documentation states that if a situation isn't covered by any of the entries in pg_hba.conf, then the connection will be killed.

There are about a billion different permutations of the above that you can go through. Be aware that when specifying PASSWORD in pg_hba.conf, you are telling pgsql that clear-text usernames and passwords will be shipped over the wire. Instead of specifying PASSWORD, you will want to examine the CRYPT option (which, unsurprisingly, encrypts the password before it sends it to the server).

Page 3 of 4

This article was originally published on December 28, 2000

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date