Postgresql installation and configuration

url: "/Installation_et_configuration_de_PostgreSQL/" title: "PostgreSQL Installation and Configuration" description: "A comprehensive guide to installing, configuring, and managing PostgreSQL databases including user management, backups, and basic SQL operations." categories: ["PostgreSQL", "Database"] date: "2012-11-16T08:55:00+02:00" lastmod: "2012-11-16T08:55:00+02:00" tags: ["PostgreSQL", "Database", "SQL", "Backup", "User Management"]

Introduction

PostgreSQL is an object-relational database management system (ORDBMS). It is a free tool available under a BSD-like license.

This system competes with other database management systems, both free (like MySQL and Firebird) and proprietary (like Oracle, Sybase, DB2, and Microsoft SQL Server). Like the Apache and Linux projects, PostgreSQL is not controlled by a single company but is based on a global community of developers and companies.

Installation

To install PostgreSQL:

1

apt-get install postgresql postgresql-client

This creates the postgres user who has rights over the database. To initialize a PostgreSQL database in the postgres user's $HOME (/var/lib/postgres on Debian) (the installation on Debian does this automatically):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

> su postgres
> cd
> /usr/lib/postgresql/8.3/bin/initdb -D data
The files in this cluster will belong to user "postgres".
The server process must also be owned by this user.

The cluster will be initialized with locale fr_FR@euro.
The default database encoding has been set accordingly to LATIN9.

creating directory data... ok
creating subdirectories... ok
selecting default max_connections... 100
selecting default shared_buffers/max_fsm_pages... 24MB/153600
creating configuration files... ok
creating template1 database in data/base/1... ok
initializing pg_authid... ok
initializing dependencies... ok
creating system views... ok
loading system objects' descriptions... ok
creating conversions... ok
initializing access privileges on built-in objects... ok
creating information schema... ok
vacuuming database template1... ok
copying template1 to template0... ok
copying template1 to postgres... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/bin/postgres -D data
or
    /usr/lib/postgresql/bin/pg_ctl -D data -l logfile start

Note: You need to have correct permissions on /tmp/

On Debian 5 (lenny), to initialize the main postgres database, run the following command as root:

1

pg_createcluster 8.3 main

Configuration files are located in $HOME/data, particularly pg_hba.conf for access rights management and postgresql.conf for general service configuration. On Debian, these files are symbolic links to files in /etc/postgresql/.

Configuration

User Management

Admin

First, let's change the password:

1

passwd postgres

This will modify the postgres password on the machine, but not on the database. You don't have to do this if you don't need to (e.g., if you always go through root then postgres). Now, let's define a password for the database:

1

psql -d template1 -c "alter user postgres with password 'password'"

or

1

\password postgres

This will make the user admin of the template1 database.

Note: To use web clients or graphical clients presented in the following chapters, you must define a password for "postgres".

Authentication

By default, to access PostgreSQL, you need to connect as the "postgres" user. To create a new user, you would need to create a system account for them first, which may not be desirable. To change this configuration, modify the "/etc/postgresql/pg_hba.conf" file and replace "ident sameuser" with "password" on the following two lines:

1
2
3

[...]
local  all     all                                        ident sameuser
host   all     all    127.0.0.1         255.255.255.255   ident sameuser

This becomes:

1
2
3

[...]
local  all     all                                        password
host   all     all    127.0.0.1         255.255.255.255   password

This modification avoids using system accounts and requires a password for each connection.

To allow another machine to connect to this Postgres server, add a line like this to the "/etc/postgresql/pg_hba.conf" file:

1

host   all     all    192.168.0.1       255.255.255.255   password

And modify the "/etc/postgresql/postgresql.conf" file by adding the following option so that postgres listens on all its addresses and not just "localhost":

1

listen_addresses = '*'

Restart PostgreSQL:

1

/etc/init.d/postgresql restart

Creation

To create a user:

1
2
3
4
5

> createuser toto
Is the new role a superuser? (y/n) n
Can the new user create databases? (y/n) y
Can the new user create users? (y/n) n
CREATE USER

By default, this user has no password. To assign one:

1

psql -d template1 -c "alter user toto with password <password>"

Suppression

To delete a user:

1

drop user toto

Database Management

Creation

The following command creates the "mybase" database for the user "toto" using the "UNICODE" encoding:

1

CREATE DATABASE <database_name> owner <username>;

Be careful with the table encoding: LATIN9, LATIN1, UNICODE, etc.

Suppression

To delete a database:

1

DROP DATABASE <mybase>

Listing

To list existing databases:

1

psql -l

Backing Up a Database

To back up a database:

1

pg_dump DATABASE_NAME > FILE_NAME

Backing Up All Databases

To back up all databases at once:

1

pg_dumpall > FILE_NAME

Backups on Very Large Databases

It seems that by passing certain parameters, it is easier to restore databases:

1

pg_dump -Ft DATABASE_NAME > FILE_NAME

https://www.postgresql.org/docs/8.1/static/app-pgdump.html Note: Be aware of some limitations in "hot" backups.

Restoration

To restore a database, first create an empty database, then import:

1

psql <newbase> < FILE_NAME

Displaying All Queries

You can increase the log level to see all queries that pass through. These queries will be recorded in a file.

To set up a higher log level, edit these lines in the PostgreSQL configuration:

1
2
3
4
5
6

[...]
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'
[...]

Just restart your PostgreSQL to see logs appear in /var/lib/postgresql//main/pg_log

Usage

You can now "use" your database with the PostgreSQL command line client:

1
2

> psql -h hostname -U user -d database
mybase=#

Here are some useful commands to remember:

1
2
3
4
5
6
7
8

\l = list databases
\d = list tables
\q = quit
\h = help
SELECT version(); = PostgreSQL version
SELECT current_date; = current date
\i file.sql = read instructions from file.sql
\d table = describe a table (like DESCRIBE in MySQL)

Creating and Deleting Tables

Here are the different data types for table fields:

1
2
3
4
5
6
7
8
9

CHAR(n)
VARCHAR(n)
INT
REAL
DOUBLE PRECISION
DATE
TIME
TIMESTAMP
INTERVAL

Note: You can also define your own data types.

The creation syntax:

1

CREATE TABLE my_table (col1 TYPE, [...], coln TYPE);

Deletion:

1

DROP TABLE my_table;

As a small example taken from the PostgreSQL documentation:

1
2
3
4
5
6
7

CREATE TABLE weather (
    city            VARCHAR(80),
    temp_lo         INT,           -- low temperature
    temp_hi         INT,           -- high temperature
    prcp            REAL,          -- precipitation
    DATE            DATE
);

Data Extraction

Nothing beats examples:

1
2
3
4

SELECT * FROM weather;
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, DATE FROM weather;
SELECT * FROM weatherWHERE city = 'San Francisco' AND prcp > 0.0;
SELECT DISTINCT city FROM weather ORDER BY city;

With joins:

1
2
3
4
5
6
7

SELECT * FROM weather, cities WHERE city = name;
SELECT weather.city, weather.temp_lo, cities.location 
FROM weather, cities WHERE cities.name = weather.city;
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather LEFT OUTER JOIN cities ON 
(weather.city = cities.name);
SELECT * FROM weather w, cities c WHERE w.city = c.name;

With functions (Aggregate Functions):

1

SELECT MAX(temp_lo) FROM weather;

Note that "Aggregate Functions" cannot be used in the WHERE clause. So the following query is incorrect:

1

SELECT city FROM weather WHERE temp_lo = MAX(temp_lo);

You should do instead:

1
2

SELECT city FROM weather WHERE 
temp_lo = (SELECT MAX(temp_lo) FROM weather);

You can, of course, use "GROUP BY ...", "HAVING ...", etc.

Data Updates

Still with an example:

1
2

UPDATE weather SET temp_hi = temp_hi - 2, 
temp_lo = temp_lo - 2 WHERE DATE > '1994-11-28';

Data Deletion

Again with an example:

1

DELETE FROM weather WHERE city = 'Hayward';

To delete all data from a table:

1

DELETE FROM weather;

Querying a Database Size

1

SELECT pg_size_pretty(pg_database_size('database_name'));

FAQ

Tsearch

If an application requires tsearch2 for example, you need to install a package:

1

aptitude install postgresql-contrib-8.2

Use your corresponding version number at the end of the package.

And finally, you need to patch the database in question:

1

psql wikidb < /usr/share/postgresql/8.2/contrib/tsearch2.sql

Here wikidb is my database, and 8.2 is the postgres version again.

UTF8 Encoding Doesn't Match the Locale

The annoying error that bugged me. This happens when you want to create a database that's not in the server's current encoding:

1
2

createdb: database creation failed: ERROR: UTF8 encoding does not match locale fr_FR@euro of the server
DETAIL: The LC_CTYPE server parameter requires the LATIN9 encoding.

To fix the problem, first check that the locales are good at the OS level:

1

dpkg-reconfigure locales

Then, let's say I want UTF-8, I set my environment variables properly:

1
2
3

export LC_ALL=fr_FR.UTF-8
export LANG=fr_FR.UTF-8
export PGCLIENTENCODING=fr_FR.UTF-8

Now I can list the postgres environment variable:

1
2
3
4
5

$ postgres=# show lc_ctype;
  lc_ctype   
 -------------
 fr_FR.UTF-8
(1 line)

Normally if you create a new database, the encoding will now be UTF-8. If that still doesn't change anything, you must run this, but it will put all your future databases in this format:

1

initdb -E UTF-8

Now try creating a database again.

Resources

• PostgreSQL Official Website
• https://www.postgresql.org/docs/7.3/static/multibyte.html
• System Views in PostgreSQL 8.3