Erik Dahlstrand

Writing on web development and server management.

Install PostgreSQL on Ubuntu

You can get the latest stable version of PostgreSQL from the PostgreSQL PPA on Launchpad. Just change lucid for the version you are using.

1
2

echo "deb http://ppa.launchpad.net/pitti/postgresql/ubuntu lucid main" > /etc/apt/sources.list.d/postgresql-lucid.list
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8683D8A2

Then run the following commands:

1
2

apt-get update
apt-get install postgresql

Don’t forget to open firewall:

1
2

ufw allow 5432/TCP
ufw allow 5432/UDP

Setup

Re-create main database cluster with other locale

If you’re not happy with the default locale (collation order, number formatting, translated messages) you can re-create the database cluster using another locale. (You can override the locale for a specific database upon creation.)

1
2
3
4
5

service postgresql stop
cd /var/lib/postgresql/9.1
rm -rf main
mkdir main
chown -R postgres:postgres main

1
2

su - postgres
initdb --locale=sv_SE.UTF-8 -A md5 -W -D main

When you re-create the main database cluster you also have to generate new certificates:

1
2
3
4
5
6

cd /var/lib/postgresql/9.1/main
openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key

Check the new locale settings:

1
2
3

pg_controldata main | grep LC
LC_COLLATE:               sv_SE.UTF-8
LC_CTYPE:                 sv_SE.UTF-8

Securing postgres User Account

Changing the postgres operating system password:

1

passwd postgres

Changing the postgres database password:

1
2
3

su - postgres
psql
\password

Configuring access to the PostgreSQL Server

Make sure postgres user can login using operating system account. All other database users must login using md5 password.

/etc/postgresql/9.1/main/pg_hba.conf

1
2

local   all         postgres                          ident
local   all         all                               md5

Password-less login

The file .pgpass in a user’s home directory can contain passwords to be used if the connection requires a password (and no password has been specified otherwise).

This file should contain lines of the following format:

~/.pgpass 
1

hostname:port:database:username:password

Each of the first four fields can be a literal value, or *, which matches anything.

1

chmod 0600 ~/.pgpass

PostgreSQL Server Applications

Frequently used server applications:

1
2
3

initdb          # create a new PostgreSQL database cluster
pg_controldata  # display control information of a PostgreSQL database cluster
pg_ctl          # start, stop, or restart a PostgreSQL server

PostgreSQL Client Applications

All client applications will by default connect with the database user name that is equal to the current operating system user name. Specify the -U option to override this.

Frequently used client applications:

1
2
3
4
5
6
7
8
9
10

createdb        # create a new PostgreSQL database
createuser      # define a new PostgreSQL user account
dropdb          # remove a PostgreSQL database
dropuser        # remove a PostgreSQL user account
pg_config       # retrieve information about the installed version of PostgreSQL
pg_dump         # extract a PostgreSQL database into a script file or other archive file
pg_restore      # restore a PostgreSQL database from an archive file created by pg_dump
psql            # PostgreSQL interactive terminal
reindexdb       # reindex a PostgreSQL database
vacuumdb        # garbage-collect and analyze a PostgreSQL database

psql examples

psql is a terminal-based front-end to PostgreSQL.

1

psql -U username

Frequently used psql commands:

1
2
3
4
5
6
7

\c        [dbname]     # connect to new database
\d                     # list tables, views, and sequences
\d        name         # describe table, view, sequence, or index
\dt       [pattern]    # list tables
\du       [pattern]    # list roles (users)
\l                     # list all databases
\password [username]   # securely change the password for a user

You can also execute SQL queries:

1

SELECT * FROM users;

Common tasks

Examples in psql, SQL and client application as appropriate.

Create a user

1

createuser -SREP username  # no-superuser, no-createrole, pwprompt, encrypted

1

CREATE ROLE username WITH CREATEDB LOGIN ENCRYPTED PASSWORD 'password';

Reset user password

1

\password [username]

1

ALTER USER username WITH ENCRYPTED PASSWORD 'password';

Delete a user

1

dropuser username

1

DROP ROLE username;

Create a database

1

createdb -O username dbname

1

CREATE DATABASE dbname WITH OWNER username;

To create a database with collation other than default:

1

createdb -l da_DK.utf8 -O username dbname

Delete a database

1

dropdb dbname

1

DROP DATABASE dbname;

Rename a database

1

ALTER DATABASE dbname RENAME TO new_dbname;

Delete a database table

1
2

DROP TABLE name [CASCADE];
-- CASCADE automatically drop objects that depend on the table (such as views)

Routine Database Maintenance Tasks

http://www.postgresql.org/docs/9.1/static/maintenance.html

Backup and Restore

http://www.postgresql.org/docs/9.1/static/backup.html

You cannot overwrite an existing database on restore. Run the following commands (logged in as postgres).

1
2
3

dropdb dbname
createdb -O username dbname
psql -U username -d dbname -f pgdump-dbname.110405-2117.sql

Comments