Presented by Keith Fiske / @keithf4
Senior Database Engineer @ Crunchy Data Solutions
(pg_partman, pg_monitor, pg_extractor, mimeo)
Follow along at
http://slides.keithf4.com/pg_admin_training_centos
sudo -s
gedit &
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql11-server postgresql11-contrib
sudo PGSETUP_INITDB_OPTIONS="--data-checksums" /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11
systemctl status postgresql-11
...
sudo -iu postgrespsql
postgres=# show data_directory;
# TYPE DATABASE USER ADDRESS METHOD
host replication replication 127.0.0.1/32 md5
host replication training 127.0.0.1/32 trust
postgres=# select pg_reload_conf();
OR
sudo systemctl reload postgresql-11
SELECT * FROM pg_hba_file_rules;
postgres=# CREATE ROLE training WITH LOGIN SUPERUSER;
postgres=# CREATE DATABASE training;
training=# CREATE ROLE replication WITH LOGIN REPLICATION;
training=# \password training
training=# \password replication
...
sudo systemctl restart postgresql-11
host replication replication 127.0.0.1/32 md5
training=# SELECT * FROM pg_create_physical_replication_slot('training_replica');
training=# SELECT * FROM pg_replication_slots;
pg_basebackup -h 127.0.0.1 -U replication -D /var/lib/pgsql/11/replica -R -Xs -P -S training_replica -v
training=# SELECT * FROM pg_replication_slots;
...
port = 5444
standby_mode = 'on'
primary_conninfo = 'host=127.0.0.1 port=5432 user=replication password=password'
primary_slot_name = 'training_replica'
recovery_target_timeline='latest'
...
sudo cp /usr/lib/systemd/system/postgresql-11.service /etc/systemd/system/postgresql-11-replica.service
Environment=PGDATA=/var/lib/pgsql/11/replica/
sudo systemctl daemon-reload
sudo systemctl enable postgresql-11-replica
sudo systemctl start postgresql-11-replica
sudo systemctl status postgresql-11-replica
...
started streaming WAL from primary at 0/3000000 on timeline 1
training=# SELECT * FROM pg_stat_replication;
training=# SELECT * FROM pg_replication_slots;
training=# CREATE TABLE testing (
id bigint primary key,
stuff text,
inserted_at timestamptz default now());
training=# INSERT INTO testing (id) values (generate_series(1,1000));
psql -p 5444
training=# SELECT pg_is_in_recovery();
...
pg_dumpall -g -f /home/training/globals.sql
pg_dump -Fc training -f /home/training/training.pgr -v
...
mkdir /home/training/mydb
chmod 700 /home/training/mydb
/usr/pgsql-11/bin/initdb --data-checksums -D /home/training/mydb
port = 5888
unix_socket_directories = '/home/training/mydb'
/usr/pgsql-11/bin/pg_ctl -D /home/training/mydb start
psql -h localhost -p 5888 -d postgres -f /home/training/globals.sql -a
psql -h localhost -p 5888 postgres
postgres=# create database new_training;
pg_restore -h localhost -p 5888 -d new_training -v /home/training/training.pgr
...
/usr/pgsql-11/bin/pg_ctl stop -m fast -D /home/training/mydb
rm -r /home/training/mydb
...
mkdir /home/training/mybackup
pg_basebackup -h 127.0.0.1 -D /home/training/mybackup -Ft -z -Xs -P -v
mkdir /home/training/mydb2
chmod 700 /home/training/mydb2
tar xvzf /home/training/mybackup/base.tar.gz -C /home/training/mydb2/
tar xvzf /home/training/mybackup/pg_wal.tar.gz -C /home/training/mydb2/pg_wal
EDIT postgresql.conf:
port = 5888
unix_socket_directories = '/home/training/mydb2'
/usr/pgsql-11/bin/pg_ctl -D /home/training/mydb2 start
psql -h localhost -p 5888
(requires hba fix)
psql -h /home/training/mydb2/ -p 5888
...
/usr/pgsql-11/bin/pg_ctl stop -m fast -D /home/training/mydb2
rm -r /home/training/mydb2
...
sudo yum install pgbackrest.x86_64
[global]
repo1-path=/var/lib/pgbackrest
log-level-console=info
[main]
pg1-path=/var/lib/pgsql/11/data
retention-full=2
sudo mkdir /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest/
sudo chown postgres:postgres /var/lib/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
...
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_mode = on
max_wal_senders = 10
wal_level = replica
sudo systemctl reload postgresql-11
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check
...
sudo -u postgres pgbackrest --stanza=main backup
sudo -u postgres pgbackrest --stanza=main backup
sudo -u postgres pgbackrest --stanza=main --type=diff backup
sudo -u postgres pgbackrest info
...
sudo -iu postgres
mkdir /var/lib/pgsql/11/br_restore
chmod 700 /var/lib/pgsql/11/br_restore
pgbackrest restore --stanza=main --db-path=/var/lib/pgsql/11/br_restore
/usr/pgsql-11/bin/pg_ctl start -D /var/lib/pgsql/11/br_restore/
/usr/pgsql-11/bin/pg_ctl stop -m fast -D /var/lib/pgsql/11/br_restore/
rm -r /var/lib/pgsql/11/br_restore
...
sudo systemctl stop postgresql-11
sudo systemctl status postgresql-11
/var/lib/pgsql/11/replica/log/...
/usr/pgsql-11/bin/pg_ctl promote -D /var/lib/pgsql/11/replica
vacuumdb --all --analyze-in-stages
SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS replica_lag;
SELECT client_hostname
, client_addr
, pg_wal_lsn_diff(pg_stat_replication.sent_lsn,
pg_stat_replication.replay_lsn) AS byte_lag
FROM pg_stat_replication;
CREATE ROLE monitoring WITH LOGIN;
GRANT pg_monitor TO monitoring;
local all monitoring trust
sudo systemctl reload postgresql-11
select max_connections
, total_used
, coalesce(round(100*(total_used/max_connections)),0) as pct_used
, idle
, idle_in_txn
, ((total_used - idle) - idle_in_txn) as active
, (select coalesce(extract(epoch from (max(now() - query_start))),0) from
pg_stat_activity where state = 'idle in transaction') as max_idle_in_txn
, (select coalesce(extract(epoch from (max(now() - query_start))),0) from
pg_stat_activity where state <> 'idle') as max_txn_time
from (select count(*) as total_used
, coalesce(sum(case when state = 'idle' then 1 else 0 end),0) as idle
, coalesce(sum(case when state = 'idle in transaction' then 1 else 0 end),0) as idle_in_txn
from pg_stat_activity)
x join (select setting::float AS max_connections FROM
pg_settings WHERE name = 'max_connections') xx ON (true);
WITH max_age AS (
SELECT 2000000000 as max_old_xid
, setting AS autovacuum_freeze_max_age
FROM pg_catalog.pg_settings
WHERE name = 'autovacuum_freeze_max_age')
, per_database_stats AS (
SELECT datname
, m.max_old_xid::int
, m.autovacuum_freeze_max_age::int
, age(d.datfrozenxid) AS oldest_current_xid
FROM pg_catalog.pg_database d
JOIN max_age m ON (true)
WHERE d.datallowconn)
SELECT max(oldest_current_xid) AS oldest_current_xid
, max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
, max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
FROM per_database_stats;
-[ RECORD 1 ]---------------------+----------
oldest_current_xid | 496452209
percent_towards_wraparound | 25
percent_towards_emergency_autovac | 99
postgres=# show autovacuum_freeze_max_age ;
-[ RECORD 1 ]-------------+----------
autovacuum_freeze_max_age | 500000000
SELECT current_database() as dbname, schemaname, relname
, seq_scan, seq_tup_read
, idx_scan, idx_tup_fetch
, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd
, n_live_tup, n_dead_tup
, vacuum_count, autovacuum_count
, analyze_count, autoanalyze_count
FROM pg_catalog.pg_stat_user_tables;
SELECT current_database() as dbname
, n.nspname as schemaname
, c.relname
, pg_total_relation_size(c.oid) as size_bytes
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE NOT pg_is_other_temp_schema(n.oid)
AND relkind IN ('r', 'm', 'f');
SELECT s.datname as dbname
, xact_commit, xact_rollback
, blks_read, blks_hit
, tup_returned, tup_fetched
, tup_inserted, tup_updated, tup_deleted
, conflicts
, temp_files, temp_bytes
, deadlocks
FROM pg_catalog.pg_stat_database s
JOIN pg_catalog.pg_database d on d.datname = s.datname
WHERE d.datistemplate = false;
SELECT datname as dbname
, pg_database_size(datname) as bytes
FROM pg_catalog.pg_database
WHERE datistemplate = false;
SELECT sum(pg_relation_size(c.oid)) as size_table
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE relkind in ( 'm', 'r', 'f')
AND NOT pg_is_other_temp_schema(n.oid);
SELECT sum(pg_relation_size(c.oid)) as size_index
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE (relkind = 'i')
AND NOT pg_is_other_temp_schema(n.oid);
SELECT count(*) AS total_count
, sum(size) AS total_size_bytes
, 100 * sum(size) / (SELECT setting::int*1024*1024 FROM pg_settings WHERE name = 'max_wal_size') AS perc_max
, min(modification)
FROM pg_ls_waldir();