Postgres

General

Glossary

PEM

Postgres Enterprise Manager

PPAS

Postgres Plus Advanced Server

WAL

At all times, PostgreSQL maintains a WAL (Write Ahead Log) in the pg_xlog/ subdirectory of the cluster’s data directory. The log describes every change made to the database’s data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the backup and then replay from the backed-up WAL files to bring the backup up to current time.

Architecture

One process per user, NO THREAD ! Processes are managed by Postmaster that acts as a listener for new connection and as a supervisor to restart them.

The term "buffer" is usually used for blocks in memory.

7500 concurrent user → connection pooling

16MB

Cluster

A cluster is a collection of databases. Clusters have separate: * data directory * TCP port * set of processes

To create a cluster execute the following comming with the postgres user (! not root !): [postgres]$ initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'

To create a second cluster on the same machine you need to: * as root, create a DATA directory * as root, change owner of the DATA directory to enterprisedb or postgres (depending on the version of postgres enterprise or community) * as postgres (or enterprisedb), do: initdb -D '/var/lib/postgres/data'

There is a little tricky behavior with the second cluster when you want to connect with a client. By default, connections will be refused for user "enterprisedb" …​ You need to change the pg_hba file and set "trust" for enterprisedb …​ Then set a password with the client and put it pack to md5.

host all enterprisedb 192.168.104.0/24 trust

NixOS

services.postgresql = {
    enable = true;
    authentication = ''
      local saltstack all trust
    '';
  };
CREATE USER vagrant SUPERSUSER LOGIN; (1)

CREATE USER salt LOGIN; (2)
CREATE DATABASE saltstack WITH owner = 'salt';
ALTER USER salt WITH password 'saltpass';

psql saltstack -U salt (3)
1 as root
2 as vagrant
3 as vagrant, check that you can connect to the db

Tips

Allocate a multiple records select in a variable in psql
CREATE OR REPLACE FUNCTION notify_result() RETURNS TRIGGER AS $$

DECLARE
notification jsonb;
chan text;

BEGIN

-- Get the user as the name of the channel
SELECT load->>'user' into chan from jids where jid = NEW.jid;
-- This is not working because salt_returns table haven't been filled in yet ...
notification := (SELECT array_to_json(array_agg(row_to_json(t))) from (SELECT r.full_ret FROM salt_returns r where r.jid = NEW.jid) t);

-- Execute pg_notify(channel, notification)
PERFORM pg_notify(chan, NEW.jid);

-- Result is ignored since this is an AFTER trigger
RETURN NULL;
END;

$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS notify_result on jids;
CREATE TRIGGER notify_result AFTER INSERT ON jids
    FOR EACH ROW EXECUTE PROCEDURE notify_result();
COPY
COPY edbstore.categories TO '/tmp/test.csv' WITH (FORMAT 'csv');
Backup & Recovery

for small DB, we can use a sql dump several time a day:

pg_dump dbname | gzip > filename.gz
pg_dump dbname | split -b 1m - filename

psql dbname < infile
Shell
psql -h 192.168.14.62 -W -U pradermecker postgres < create_PGPUPPETDB.sql

export PGPASSWORD=dbpasswordforpuppetdb
ssh puppetmaster-prod 'sudo -u postgres pg_dump puppetdb ' | psql -h 192.168.14.62 -U puppetdb -w PGPUPPETDB

for t in $(pqsl -U enterprisedb -d edbstore -t -c "select tablename from pg_tables where tableowner='edbstore'"); do
  pg_dump -t edbstore.$t -U enterprisedb edbstore > $t.sql;
done

select tablename from pg_tables where tableowner='edbstore';
select table_name from information_schema.tables where table_schema='edbstore';

Replication

  • Hot Streaming Replication (Warm Streaming Replication or Log WAL Shipping is deprecated) There is a daemon process started by the PostMaster

We don’t have to start the slave before the master. The slave can just wait for a master to start up.

  1. First shutdown the master and set it up for replication by:

    1. change postgres.conf

      wal_level = hot_standby
max_wal_senders = 4
wal_keep_segments = 32
      archive_mode = on
archive_command = 'cp %p /data/archive/%f'

    2. change pg_hba.conf:

      host  replication  repuser slaveip/32  md5

  2. Configure the pg_hba.conf of the slave:

    host  replication  repuser masterip/32  md5

  3. Initialize the cluster

On a local server, you can just copy the data folder from the master to the slave or pg_basebackup -h localhost -D /opt/PostgresPlus/9.3AS/data1 but on a real set up you would follow these steps:

  1. on the master:

    postgres=# select pg_start_backup('cluster_init');

  2. on the slave:

    rsync -avz --delete --inplace --exclude-from=/srv/pgsql/do-not-sync  root@195.244.165.68:/srv/pgsql/data/ /srv/pgsql/data (1)
    1 with the postgres user

  3. on the master

    postgres=# select pg_stop_backup();

" PAX process

Select * from pg_stat_activity
select * from pg