• PostgreSQL Tips
  • Overview
  • Initial Setup on Debian Systems
  • General
    • psql Client
    • Users
    • Databases
  • Sequences
    • Create
    • Drop
    • Restart
    • Set Value
    • Current Value
    • Last value within current session
    • Next Value
  • Debugging
  • Maintenance
  • Dumping Data
    • Full dump of all databases in cluster
    • Restoring a full dump
    • Full dump of database in compressed binary format
    • Restore compressed file
    • Data plus drop table on restore (text output)
    • Data Only (text output)
    • Schema Only (text output)
  • Quoting
  • Type Casting
  • Handling Errors on Import
  • Trouble Shooting
    • Invalid Byte Sequence

PostgreSQL Tips

Overview

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

Also check out the PostgreSQL FAQ.

---

Initial Setup on Debian Systems

If you're using sudo, become root with sudo -i.

As root, become the postgres user, then create a role with superuser privileges that matches your unix username:

# su - postgres
$ createuser -drs $USER_NAME

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

Saving psql Preferences

Generally when psql executes it runs commands it finds in the system-wide startup file (psqlrc) and in a user's personal startup file. e.g.

    cat ~/.psqlrc
    \pset pager off
    \set AUTOCOMMIT off

See psqlrc and ~/.psqlrc in the FILES section of the psql man pages.

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'

To safely change the password without exposing it, use the psql \password command:

psql \password username

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;

Restart

• ALTER SEQUENCE foo RESTART;

Next call to nextval will return the start value of the sequence.

• ALTER SEQUENCE foo RESTART WITH 10;

Next call to nextval will return the specified value, 10.

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 pgdropcluster and pgcreatecluster 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

Type Casting

Some function calls may need a type cast. E.g.:

SELECT round(random() * 100, 1);

ERROR:  function round(double precision, integer) does not exist
LINE 1: select round(random() * 100, 1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Apply the type cast as either:

SELECT round((random() * 100)::numeric, 1);

or

SELECT round(cast(random() * 100 as numeric), 1);

Handling Errors on Import

The ON_ERROR_STOP setting will halt the import on the first error, giving the opportunity to understand the cause.

$ psql -v ON_ERROR_STOP=on -f backup.sql $MY_DB

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, PostgreSQLmacOS

• Viki
• Business
• Tech