Table of Contents
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/
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
- adjust the configuration as needed
- check logfiles:
Be aware of the following defaults:
- PostgreSQL will listen on
- 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.
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
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
psqlis the CLI client (in the MySQL world you would use 'mysql')
pg_dumpis the backup client (equivalent to 'mysqldump')
createdbto create a database and 'dropdb' to drop it
pg_lsclustersobtain some basic information
pg_ctlclustercontrol 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 (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
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
If you want to dig deeper head to:
How to get started:
List all databases:
List all databases with disk usage:
Connect to a database:
Show tables in database:
… or all tables with disk usage:
Here is how to upgrade from postgreSQL 9.5 to 9.6 on Ubuntu Xenial:
- verify what is installed
- additionally install postgreSQL 9.6
- stop both postgreSQL servers and remove the automatically created and unused 9.6 “main” cluster with pg_dropcluster
- upgrade your 9.5 main cluster with pg_upgradecluster
- start postgreSQL and verify the upgraded cluster works
- drop the old 9.5 main cluster
- 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
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 firstname.lastname@example.org systemctl stop email@example.com 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`
#systemctl enable firstname.lastname@example.org #systemctl start email@example.com 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