PostgreSQL

From Segfault
Jump to: navigation, search

Installation

    dnf install postgresql-server postgresql                       # Fedora
apt-get install postgresql postgresql-client phppgadmin php5-fpm   # Debian, Ubuntu

After the installation, we need add a database user too:

  1. Create a (Unix) system user.
  2. Use the predefined postgres user to connect to the database.
  3. Create a database user within the database.
$ sudo useradd -m -U -s /bin/bash pguser                           # --create-home, --user-group
$ sudo -u postgres LESS="-X" psql                                  # Don't clear the screen after using the pager

postgres=# create user pguser with password 's3cr3t' CREATEDB SUPERUSER;
postgres=# create database testdb owner pguser;

postgres=# select usename, usesysid, usecreatedb, usesuper, passwd from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper |               passwd                
----------+----------+-------------+----------+-------------------------------------
 postgres |       10 | t           | t        | 
 pguser   |    16384 | f           | t        | md5a4d80eac9ab26a4a2da04125bc2c096a

postgres=# select datname, datcollate, datctype, datistemplate, datallowconn, dattablespace from pg_database;
  datname  | datcollate  |  datctype   | datistemplate | datallowconn | dattablespace 
-----------+-------------+-------------+---------------+--------------+---------------
 template1 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |          1663
 template0 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |          1663
 postgres  | en_US.UTF-8 | en_US.UTF-8 | f             | t            |          1663
 testdb    | en_US.UTF-8 | en_US.UTF-8 | f             | t            |          1663

postgres=# \q

Let's connect to our newly created database:

$ sudo -u pguser psql -h localhost -p 5432 testdb
testdb=> \x on \t on
Expanded display is on.
Tuples only is on.

testdb=> show server_version;
server_version | 9.4.4

testdb=> \conninfo 
You are connected to database "testdb" as user "pguser" on host "localhost" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Configuration

Access

With PostreSQL, authentication appears to be a mix of system and application level methods.

For some reason, pg_dump would not work:

$ pg_dump -U pguser -W testdb
Password: 
pg_dump: [archiver (db)] connection to database "testdb" failed: FATAL:  Peer authentication failed for user "pguser"

The logfile had:

pguser@testdb LOG:  provided user name (pguser) and authenticated user name (root) do not match
pguser@testdb FATAL:  Peer authentication failed for user "pguser"
pguser@testdb DETAIL:  Connection matched pg_hba.conf line 90: \
                "local   all             all                                     peer"

So, for local connections, the peer authentication[1] is used:

$ head -n +92 /etc/postgresql/9.4/main/pg_hba.conf | tail -4
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

Using pg_dump over the network works:

$ pg_dump -U pguser -h localhost -W testdb | wc -l
Password: 
213

We could also use our database user to run pg_dump:

$ sudo -u pguser pg_dump testdb | wc -l
213

Let's change the local authentication method to password-based:

$ head -n +90 /etc/postgresql/9.4/main/pg_hba.conf | tail -2
# "local" is for Unix domain socket connections only
local   all             all                                     md5

$ sudo pkill -1 postgres
$ pg_dump -U pguser -W testdb | wc -l
Password: 
213

Usage

Connect to database postgres, then switch to database testdb

$ sudo -u pguser LESS="-X" psql postgres
postgres=# \c testdb 
You are now connected to database "testdb" as user "pguser".

Create a table, add some rows:

testdb=> create table users (id serial primary key not null, name text not null, email text not null);
CREATE TABLE

testdb=> insert into users (name, email) values ('alice', 'alice@example.org');
INSERT 0 1

testdb=> insert into users (name, email) values ('bob', 'bob@example.org');
INSERT 0 1

testdb=> select * from users;
 id | name  |       email       
----+-------+-------------------
  1 | alice | alice@example.org
  2 | bob   | bob@example.org
(2 rows)

Again, with different output formatting:

testdb=# \x on \t on
Expanded display is on.
Tuples only is on.

testdb=# select * from users;
id    | 1
name  | alice
email | alice@example.org
------+------------------
id    | 2
name  | bob
email | bob@example.org

Backup, Restore

To backup one database:

pg_dump -U pguser testdb > testdb.sql

Backup roles and tablespaces also:

pg_dumpall -U pguser --globals > pg_globals.sql

To dump all databases, use pg_dumpall:

sudo -u postgres pg_dumpall > pg_all.sql

Let's drop the database and restore the backup:

$ psql -U pguser postgres -c "drop database testdb;"
DROP DATABASE

$ psql -U pguser postgres -c "create database testdb;"
CREATE DATABASE

$ psql -U pguser testdb -f testdb.sql                        # We could also use < instead of -f
$ psql -U pguser testdb -c "select * from users;"
Password for user pguser: 
 id | name  |       email       
----+-------+-------------------
  1 | alice | alice@example.org
  2 | bob   | bob@example.org
(2 rows)

Replication

TBD!

Links

References