Skip to content

Database

Jump to bottom
Silvio Moioli edited this page Nov 3, 2020 · 10 revisions

PostgreSQL Tips and Tricks

HOWTO monitor currently active queries

spacewalk-sql --select-mode - <<<"SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start DESC;"

To continuously log active queries to a file, use:

while sleep 1; do spacewalk-sql --select-mode - <<<"SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start DESC;" | tee -a /tmp/query.log; done

HOWTO log slow queries

Add log_min_duration_statement to the configuration file and reload it:

echo "log_min_duration_statement = 1000" >> /var/lib/pgsql/data/postgresql.conf 
spacewalk-sql --select-mode - <<<"SELECT pg_reload_conf();"

Queries taking longer than 1 second will appear in logs with their durations. To check:

tail -f /var/lib/pgsql/data/log/`ls -t /var/lib/pgsql/data/log/ | head -1`

HOWTO analyze a query plan with PEV

A very nice tool to get a visual, browseable representation of a query plan is PEV.

Easiest way to use it is:

  • prepare a file with the query with an EXPLAIN preamble:
cat >explain.sql <<EOF
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT ...;
EOF
  • run the query via psql to capure the output in JSON format:
export PGUSER=`grep -oP "db_user ?= ?\K.*" /etc/rhn/rhn.conf`
export PGPASSWORD=`grep -oP "db_password ?= ?\K.*" /etc/rhn/rhn.conf`
export PGDATABASE=`grep -oP "db_name ?= ?\K.*" /etc/rhn/rhn.conf`
psql --quiet --no-align --tuples-only --file=explain.sql > explain.json
  • drag explain.json into

http://tatiyants.com/pev

HOWTO list database tables by disk size occupation

Copy and paste:

date

printf "\n\n\n**** DATABASE SIZES\n"
du -h /var/lib/pgsql/data

printf "\n\n\n**** TABLE SIZES\n"
spacewalk-sql --select-mode - <<<"
  WITH table_sizes AS (
    SELECT CAST(relname AS TEXT) AS table_name,
        pg_size_pretty(pg_total_relation_size(C.oid)) AS size,
        pg_total_relation_size(C.oid) AS ordering
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE nspname NOT IN ('pg_catalog', 'information_schema')
        AND C.relkind <> 'i'
        AND nspname !~ '^pg_toast'
      ORDER BY ordering DESC
      LIMIT 20
  ),
  total_size AS (
    SELECT CAST('**TOTAL**' AS TEXT) as table_name,
      pg_size_pretty(CAST(SUM(pg_total_relation_size(C.oid)) AS BIGINT)) AS size,
      -1 AS ordering
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
      AND C.relkind <> 'i'
      AND nspname !~ '^pg_toast'
  ),
  all_sizes AS (
    SELECT * FROM table_sizes UNION ALL (SELECT * FROM total_size)
  )
  SELECT table_name, size
    FROM all_sizes
    ORDER BY ordering DESC;
"

HOWTO get a database dump

There are different ways to get database dumps:

  1. pg_dump: creates a big SQL file that can be run to re-create the DB. This is a compact representation and it can be restored on any later Postgres version, so it's ideal for data analysis. Copy and paste:
mkdir /tmp/backup
chown postgres:postgres /tmp/backup
chmod 700 /tmp/backup
su - postgres -c "pg_dump --create `grep -oP "db_name ?= ?\K.*" /etc/rhn/rhn.conf` | gzip > /tmp/backup/pg_dump.gz"
  1. pg_basebackup through smdba: creates copies of internal PostgreSQL files that can be used as-is. It is less compact than pg_dump, it requires the same PostgreSQL version but it preserves some lower level properties, like previous versions of rows, stale space in tables, etc. It is thus more useful in case of performance issues. Moreover many users already use smdba for their own backup purposes, so it might be easier to obtain one.
mkdir /tmp/backup
chown postgres:postgres /tmp/backup
chmod 700 /tmp/backup
smdba backup-hot --enable=on --backup-dir=/tmp/backup
smdba backup-hot --enable=off
  1. tar of the data directory with the service down. Simplest, closest to reality, requires downtime.
rcpostgresql stop
tar czvf /tmp/raw_copy.tar.gz /var/lib/pgsql

HOWTO restore a database dump taken from pg_dump

Create a server with the matching version (eg. using sumaform and stop all services:

spacewalk-service stop
rcsalt-master stop

Then take a look at the dump and see who the main user is (in this example uyuni):

zless pg_dump.gz

...
ALTER SCHEMA logging OWNER TO uyuni;
...

Create that user with a password (susemanager in the example), and re-create the destination database ('susemanager' in the example, which is sumaform's default):

su - postgres

psql
CREATE USER susemanager WITH ENCRYPTED PASSWORD 'susemanager';
ALTER ROLE susemanager superuser;
DROP DATABASE susemanager;
CREATE DATABASE susemanager;
\q

Restore the dump to the destination database ('susemanager' in the example):

gzip -d -c /tmp/pg_dump.gz | psql susemanager --set ON_ERROR_STOP=on

BIG DUMP NOTE: if your dump is in the gigabytes of size, copying it to the server and decompressing it might be very slow and require a lot of disk space. In those cases it is more practical to do the decompression on your host with pigz, which decompresses gzipped files using multiple cores/threads, and transfer the result to a socket to be fed directly into psql.

On the destination server run: nc -l 8123 | psql susemanager --set ON_ERROR_STOP=on

(nc comes from the netcat-openbsd package and waits for data on port 8123. More about netcat)

On your host run: unpigz <pg_dump.gz | nc -N server.tf.local 8123

(unpigz comes from the pigz package and decompresses gzipped files using multiple cores/threads. More about pigz)

You can omit the --set ON_ERROR_STOP=on if you are OK with a non-perfect restore (eg. for data analysis purposes you might not want to stop the process if pl/tcl is missing).

User passwords and email addresses

Now change all registered users' passwords to admin and all email addresses to a dummy address, otherwise the user will get bombed with error emails.

psql susemanager
BEGIN;
UPDATE web_contact set password='$1$IrobqEPV$9lT.npWHmOezFreAJg8Dw/';
UPDATE web_user_personal_info set email = '[email protected]';
COMMIT;
SELECT
  wc.login,
  string_agg(g.name, ', ') AS groups
FROM web_contact wc
  JOIN rhnUserGroupMembers gm ON gm.user_id = wc.id
  JOIN rhnUserGroup g ON g.id = gm.user_group_id
GROUP BY login
ORDER BY login;
\q

Note usernames if you want to log in later.

If you need to use the Web UI, adjust /etc/rhn/rhn.conf connection parameters and /var/lib/pgsql/data/pg_hba.conf according to the possibly changed username, password and database name. Example:

vim /etc/rhn/rhn.conf
...
db_backend = postgresql
db_user = susemanager
db_password = susemanager
db_name = susemanager
db_host = localhost
db_port = 5432


# check pg_hba.conf. There should be lines like the following (username is the third column):
vim /var/lib/pgsql/data/pg_hba.conf
local   susemanager     susemanager     md5
host    susemanager     susemanager     127.0.0.1/8     md5
host    susemanager     susemanager     ::1/128 md5
...

rcpostgresql restart
rcsalt-master start
spacewalk-service start

HOWTO Restore a database dump from an smdba or a plain tarball

spacewalk-service stop
rcsalt-master stop
rcpostgresql stop

# move away existing data
mv /var/lib/pgsql/data /var/lib/pgsql/data_orig
mkdir /var/lib/pgsql/data

# replace with data from archive
cd /var/lib/pgsql/data
tar xvf ~/base.tar.gz
chown -R postgres:postgres /var/lib/pgsql/data
chmod -R 0700 /var/lib/pgsql/data

# replace base tuning configuration, hardware is likely different
cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.orig
smdba system-check autotuning --max_connections=50

# allow access from other hosts
echo listen_addresses = \'*\' >> /var/lib/pgsql/data/postgresql.conf
echo host    all     all       0.0.0.0/0      md5 >> /var/lib/pgsql/data/pg_hba.conf

rcpostgresql start

# list Postgres users and change the password to 'susemanager'
su -l postgres
psql
\du

# there should be two roles: "postgres" and a user one (typically "susemanager")
# from here on, it is assumed "susemanager" is the user name. If it is not, please adapt
# following instructions accordingly

ALTER USER susemanager WITH ENCRYPTED PASSWORD 'susemanager';
ALTER ROLE susemanager superuser;
\q

# check pg_hba.conf. There should be lines like the following (username is the third column):
# local   susemanager     susemanager     md5
# host    susemanager     susemanager     127.0.0.1/8     md5
# host    susemanager     susemanager     ::1/128 md5
# ...

vim /var/lib/pgsql/data/pg_hba.conf

# change all SUSE Manager users' passwords to 'admin'
# and all email addresses. Otherwise the user will get bombed with error emails
PGPASSWORD=susemanager psql -h localhost -d susemanager -U susemanager
BEGIN;
UPDATE web_contact set password='$1$IrobqEPV$9lT.npWHmOezFreAJg8Dw/';
UPDATE web_user_personal_info set email = '[email protected]';
COMMIT;

SELECT
  wc.login,
  string_agg(g.name, ', ') AS groups
FROM web_contact wc
  JOIN rhnUserGroupMembers gm ON gm.user_id = wc.id
  JOIN rhnUserGroup g ON g.id = gm.user_group_id
GROUP BY login
ORDER BY login;
\q

exit

# double check that database credentials are correct (in particular the username)
vim /etc/rhn/rhn.conf

rcsalt-master start
spacewalk-service start

BIG DUMP NOTE: if your dump is in the gigabytes of size, copying it to the server and decompressing it might be very slow and require a lot of disk space. In those cases it is more practical to do the decompression on your host with pigz, which decompresses gzipped files using multiple cores/threads, and transfer the result to a socket to be fed directly into psql.

On the destination server run: nc -l 8123 | tar -xv

(nc comes from the netcat-openbsd package and waits for data on port 8123. More about netcat)

On your host run: unpigz <pg_dump.gz | nc -N server.tf.local 8123

(unpigz comes from the pigz package and decompresses gzipped files using multiple cores/threads. More about pigz)

Clone this wiki locally