PostgreSQL

PostgreSQL is pronounced “post gress queue ell”.

These are my basic PostgreSQL infos. Coming from MySQL I focused on the PostgreSQL equivalents to what I know from MySQL.

For an comprehensive comparison see MySQL vs PostgreSQL

The excellent documentation lives here: http://www.postgresql.org/docs/

Installation

The Debian installation is straight forward:

apt-get install postgresql

PostgreSQL maintains an apt repository of packages for Debian and Ubuntu, see: https://wiki.postgresql.org/wiki/Apt
Several releases of PostgreSQL are supported.

Post installation steps:

  • set a password for the superuser postgres
  • adjust the configuration as needed
  • check logfiles: /var/log/postgresql/

Be aware of the following defaults:

  • PostgreSQL will listen on 127.0.0.1:5432
  • default cluster: main
  • default database: template1
  • default schema: public

A few words about PostgreSQL “clusters”:
Before you can do anything, you must initialize a database storage area on disk. That's what is called a database cluster (a collection of databases managed by a single instance of a running database server).

In file system terms, a database cluster will be a single directory under which all data will be stored.

Schema
You can think of Schemas as something like directories at the OS level. But you can't create “subdirectories” or nest schemas.

If you don't specifiy any schema, the “public” schema will be automatically selected.

Schemas are useful to organize access to databases or group database objects into logical groups.

If you're still confused, I recommend reading the official docs: http://www.postgresql.org/docs/9.1/static/ddl-schemas.html

One last thing about schemas:
you'll find the system catalog schema with all the tables and buil-in data types, functions and operators in the pg_catalog schema.

Configuration

Where the MySQL configuration is somewhat “flat” PostgreSQL comes with several files:

/etc/postgresql
└── 9.1
    └── main
        ├── environment
        ├── pg_ctl.conf
        ├── pg_hba.conf
        ├── pg_ident.conf
        ├── postgresql.conf
        └── start.conf

2 directories, 6 files

CLI Tools

  • psql is the CLI client (in the MySQL world you would use 'mysql')
  • pg_dump is the backup client (equivalent to 'mysqldump')
  • createuser or 'dropuser'
  • createdb to create a database and 'dropdb' to drop it
  • pg_lsclusters obtain some basic information
  • pg_ctlcluster control cluster (start|stop|restart|reload|promote)

To work with the CGI tool psql it is best practice to do this as the postgres system user:

# if you're root, become postgres:
su - postgres

psql

psql (9.1.14)
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

pg_lsclusters

Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5432 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log

Users, Roles and Databases

The default client authentication setup allows any local user to connect to the database and even become the database superuser.

You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account.

This user has superuser status. A database superuser bypasses all permission checks, except the right to log in or the right to initiate replication.

postgres, the superuser
You'll want to create a password for this user. Enter psql as usual and set the password:

psql (9.1.14)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#

Access from localhost via Unix-domain sockets would still be possible without a password as long as you don't edit the default pg_hba.conf file.

If you want to dig deeper head to:

psql

How to get started:

List all databases:

\l

List all databases with disk usage:

\l+

Connect to a database:

\c db-name

Show tables in database:

\dt

… or all tables with disk usage:

\dt+

Describe table:

\d tablename

Quit psgl:

\q

Upgrade postgreSQL

Here is how to upgrade from postgreSQL 9.5 to 9.6 on Ubuntu Xenial:

  1. verify what is installed
  2. additionally install postgreSQL 9.6
  3. stop both postgreSQL servers and remove the automatically created and unused 9.6 “main” cluster with pg_dropcluster
  4. upgrade your 9.5 main cluster with pg_upgradecluster
  5. start postgreSQL and verify the upgraded cluster works
  6. drop the old 9.5 main cluster
  7. purge the old 9.5 packages

1. verify what is installed:

dpkg -l | grep postgresql
ii  pgdg-keyring                       2017.2                                     all          keyring for apt.postgresql.org
rc  postgresql-9.3                     9.3.12-0ubuntu0.14.04                      amd64        object-relational SQL database, version 9.3 server
ii  postgresql-9.5                     9.5.9-1.pgdg16.04+1                        amd64        object-relational SQL database, version 9.5 server
ii  postgresql-client-9.5              9.5.9-1.pgdg16.04+1                        amd64        front-end programs for PostgreSQL 9.5
ii  postgresql-client-common           187.pgdg16.04+1                            all          manager for multiple PostgreSQL client versions
ii  postgresql-common                  187.pgdg16.04+1                            all          PostgreSQL database-cluster manager
ii  postgresql-contrib-9.5             9.5.9-1.pgdg16.04+1                        amd64        additional facilities for PostgreSQL

pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
Please be aware that the installation of postgresql-9.6 will automatically create a default cluster 9.6/main.
If you want to upgrade the 9.5/main cluster, you need to remove the already existing 9.6 cluster
(pg_dropcluster –stop 9.6 main, see manpage for details).

After the existing clusters are upgraded, the postgresql-9.5 and postgresql-client-9.5 packages should be removed.

2. install postgreSQL 9.6

Create /etc/apt/sources.list.d/pgdg.list

deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-9.6 postgresql-client-9.6

pg_lsclusters should show both main clusters are there:

Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main    5433 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

3. get rid of the 9.6 main cluster:

# stop both old and new servers
systemctl stop postgresql@9.5-main.service
systemctl stop postgresql@9.6-main.service
 
su - postgres -c  'pg_dropcluster 9.6 main --stop'

4. upgrade the 9.5 main cluster to version 9.6

su - postgres -c 'pg_upgradecluster 9.5 main'
 
# check and modify config for postgreSQL 9.6
# ...
 
systemctl daemon-reload
 
# restart postreSQL if you made changes to the config
kill -INT `head -1 /var/run/postgresql/9.6-main.pid`

5. verify

#systemctl enable postgresql@9.6-main.service
#systemctl start postgresql@9.6-main.service
 
systemctl start postgresql.service
pg_lsclusters

6. drop the old 9.5 main cluster:

pg_dropcluster 9.5 main

7. remove the old packages

apt-get remove postgresql-9.5 postgresql-client-9.5 postgresql-contrib-9.5
postgres.txt · Last modified: 2017-11 by tb
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0 ipv6 ready