Version 9 (modified by joshuadf, 10 years ago) (diff)


PostgreSQL is a popular open-source RDBMS included in Red Hat Enterprise Linux. provides a good Mac-packaged version, including some GUI tools (DB backup, user management, etc.).


MySQL to PostgreSQL command-line hints

MySQL's command-line tool mysql uses SQL-like syntax such as SHOW DATABASES; for RDBMS information. PostgreSQL takes a different approach in psql-- meta-commands that begin with a backslash and do not need to end with a semicolon, or reference virtual relations beginning with pg_. Here are some of the most common:

MySQL mysql PostgreSQL psql
SHOW COLUMNS; \d table-name
USE db-name; \c db-name

Other meta-commands are explained in man psql or in the Red Hat Database Manual

Changing password

ALTER USER davide WITH PASSWORD 'hu8jmn3'; 

SQL/X support

Setting up a PostgreSQL server to accept network connections

The default Red Hat EL5 PostgeSQL configuration will only accept localhost connections but not network connections. Additionally, PostgeSQL has a complex host-based authentication mechanism to control access to databases. By default, PostgreSQL uses the operating system to provide the connecting username (ident authentication), so you have to be logged in as the local user postgres to connect as superuser.

The following commands enable network connections and add rules to the host-based authentication table (pg_hba.conf) to allow the xbrain_user to connect from any machine on the subnet If you also want to log queries, edit the `log_statement` line. By default logs are in /var/lib/pgsql/data/pg_log/.

yum -y install postgresql-server
chkconfig postgresql on
# run the other commands as postgres user
su - postgres
cd /var/lib/pgsql/data
mv postgresql.conf postgresql.conf.orig
cat > postgresql.conf<<EOF
listen_addresses = '*'                  # defaults to 'localhost', '*' = all
max_connections = 100
shared_buffers = 1000                   # min 16 or max_connections*2, 8KB each
redirect_stderr = on                    # Enable capturing of stderr into log
log_directory = 'pg_log'                # Directory where log files are written
log_filename = 'postgresql-%a.log'      # Log file name pattern.
log_truncate_on_rotation = on   # If on, any existing log file of the same
log_rotation_age = 1440                 # Automatic rotation of logfiles will
log_rotation_size = 0                   # Automatic rotation of logfiles will
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
log_statement = 'none'                  # none, mod, ddl, all; look in /var/lib/pgsql/data/pg_log/
cat >>/var/lib/pgsql/data/pg_hba.conf<<EOF
# TYPE  DATABASE        USER        IP-ADDRESS    IP-MASK           METHOD
host    all             xbrain_user     md5
/etc/init.d/postgresql restart

For more information, see the PostgreSQL manual section on Authentication Methods

Next, we need to create a user, in this example with the createuser tool.

createuser --no-adduser --no-createdb --pwprompt xbrain_user
# Enter password for new user:
# Enter it again:

After creating the user we test out the TCP/IP connection:

psql template1 -U xbrain_user -h localhost
# Password: 
# template1=> \q

SSL Encryption

There are three steps to enabling SSL encryption for all client/server communications:

  1. Generate server.key and server.crt in /var/lib/pgsql/data/
  2. Add "ssl = on" to postgresql.conf
  3. Add "hostssl" lines to pg_hba.conf and restart the server

You can test with code such as "Connecting to PostgreSQL via an Encrypted Connection using SSL and JDBC".


There are several methods, but this works:

export PGDATABASE=mydb
export PGUSER=mypguser
echo "$PGHOST:5432:$PGDATABASE:$PGUSER:superpass" >> $HOME/.pgpass
chmod 0600 $HOME/.pgpass
pg_dump --format=c --host mydb > 20090522-mydb.$$
pg_restore --host --clean --dbname mydb

Outputting CSV

The psql tool has many output options, including unaligned comma-separated output. For example:

db=# select * from "Family";
 ID | name | num 
  3 | abc  | 123
  5 | bar  |   5
(2 rows)

db=# \a \t \pset fieldsep ','
Output format is unaligned.
Showing only tuples.
Field separator is ",".

db=# select * from "Family";
6,PLE 1234,6