PostgreSQL Tips

Overview

This document lists some quick hints and tips when using PostgreSQL.

Also check out the PostgreSQL FAQ.

General

psql Client

Connect using 'sameuser' authentication:

  • psql -d mydb

Connect as a specific user

  • psql -d mydb -U username

or to a specific host

  • psql -d mydb -h localhost -U username

Toggle Pager

In psql, the following command toggles whether paging is on or off

   $ psql
   postgres=> \pset pager

List Results with Expanded (Vertical) Output

postgres=> \x

Echoing Executed Commands

postgres=> \set ECHO all

or

   $ psql -a

Users

Create User

From pgsql

  • CREATE USER username PASSWORD 'secret'

From the command line

  • createuser username

Drop User

From the command line

  • dropuser username

Change Password

From pgsql

  • ALTER USER username WITH ENCRYPTED PASSWORD 'secret'

List Users

In pgsql, as a supseruser

  • \du

Databases

Create Database

Assuming you're a PostgreSQL superuser, you can create a database for another user, from the command line;

  • createdb dbname --owner=username

From pgsql

  • CREATE DATABASE dbname WITH OWNER=username ENCODING='UNICODE';

List Databases

  • su - postgres
  • psql template1

In pgsql;

  • \l

Sequences

Create

  • CREATE SEQUENCE foo START WITH 1;

Drop

  • DROP SEQUENCE foo;

Set Value

  • SELECT setval('foo', 42); Next nextval will return 43
  • SELECT setval('foo', 42, true); Same as above
  • SELECT setval('foo', 42, false); Next nextval will return 42

Current Value

  • SELECT currval('foo');

Last value within current session

  • SELECT lastval();

Next Value

  • SELECT nextval('foo');

Debugging

If you start the PostgreSQL Postmaster with a '-d 2' option, this will show the SQL statements being executed. This is usefull when running with another application such as JBoss or Jonas and you need to see what SQL is being produced.

The following command emulates the service startup script on a Mandrake 10.0 system, where the service is run under a 'postgres' user. First stop the service with 'service postgresql stop', then restart the service manually with the following command;

  • su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -o '-p 5432 -d 2' start > /var/lib/pgsql/server.log 2>&1"

Higher debug levels can be specifed for more verbose output.

Running 'tail -f /var/lib/pgsql/server.log' will show the debug output.

The server can be stopped with;

  • su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl stop -D /var/lib/pgsql/data -s -m fast"

Maintenance

PostgreSQL databases require the occasional maintenance. At the very least you need to perform the following command from time-to-time.

  • /path/to/postgres/bin/vacuumdb --all --full

The frequency which this needs to be done depends on database activity. Please refer to the PostgreSQL documentation.

Dumping Data

Full dump of all databases in cluster

  • pg_dumpall > myfile.dump

or

  • pg_dumpall | gzip -c > myfile.dump.gz

Restoring a full dump

If you need to re-initialize the database, you can use the pg_dropcluster and pg_createcluster utilities that are included in Debian 5.0 (Lenny).

Warning: This completely deletes the database cluster and removes/replaces your configuration files. Ensure you have a full backup of everything first.

   # pg_dropcluster 8.3 main --stop
   # pg_createcluster 8.3 main --start

The restore the database as follows, using a user who has PostgreSQL super user privileges:

   $ gunzip -c myfile.dump.gz | psql postgres

or perhaps

   $ gunzip -U a_pg_super_user -c myfile.dump.gz | psql postgres

Full dump of database in compressed binary format

  • pg_dump --format=c --blobs --file myfile.dump mydatabase

Restore compressed file

  • pg_restore -d mydatabase -l myfile.dump > myfile.list

  • modify and re-order items in myfile.list (comment lines out with semi-colons)

  • pg_restore -d mydatabase -L myfile.list myfile.dump

Data plus drop table on restore (text output)

Add the '--no-owner' option to exclude ownership of objects being included in the dump.

  • pg_dump --clean --column-inserts mydatabase --file myfile.sql

Data Only (text output)

This format should be fairly portable between different databases and versions of PostgreSQL.

  • pg_dump --data-only --column-inserts --no-privileges mydatabase --file myfile.sql

Schema Only (text output)

  • pg_dump --schema-only --file schema.sql

Quoting

Surrounding table and column names with double quotes in SQL statements causes PostgreSQL to treat the names case sensitively.

If case sensitivity is really required, in Hibernate you need to use back ticks around the names in the Hibernate mapping files instead of double quotes. You still use double quotes in SQL statements.

-- Frank Dean - 07 Sep 2009

Trouble Shooting

Invalid Byte Sequence

This error can occur when trying to restore data from a backup. In PostgreSQL version 8.1 validation was added to ensure invalid unicode characters could not be added to the database. If your database had invalid characters entered prior to using this version, a backup and restore may fail.

The data can be cleaned with the iconv utility provided by the libc6 package in Debian 5.0 (Lenny).

   $ iconv -c -f UTF-8 -t UTF-8 -o cleanfile.dump backup.dump

Any not UTF-8 characters will be stripped before being written to the output file.

By doing a diff on the two files you can find which data is affected.

-- Frank Dean - 24 Oct 2004

Related Topics: CygwinPostgreSQL