Common PostgreSQL patterns - Dump and restore

SQL Materialized views

Regular Views:

• Simply aliases for SQL queries, do not store data

• Require query parsing and processing every time they are used

• Can be slow for large/complex queries as query runs fully each time

Materialized Views:

• Precompute and store results just like a table

• Queries run directly on stored results, no parsing needed

• Much faster for large/complex queries, act as a cache

• Results need periodic refreshing to ensure they are up-to-date

https://www.postgresql.org/docs/current/rules-materializedviews.html

SQL admin queries

25
1
% size of table 
2
SELECT pg_size_pretty(pg_table_size('table'))
3

4
% cleanup cache
5
VACUUM full;
6
VACUUM (VERBOSE, ANALYZE) table_name;
7
VACUUM (VERBOSE, ANALYZE) tasks;
8

9
% activity
10
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;
11

12
SELECT pid, (now() - query_start) FROM pg_stat_activity ;
13
SELECT query FROM pg_stat_activity WHERE pid = 14274;
14

15

16
SELECT COUNT(*) FROM pg_stat_activity;
17
SELECT pid, query FROM pg_stat_activity WHERE query ILIKE '%ALTER%';
18

19
SELECT pg_blocking_pids(pid) as blocked_by, pid FROM pg_stat_activity WHERE pid IN (16839, 14309)
20

21
SELECT state, state_change, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = 14309
22
SELECT query FROM pg_stat_activity WHERE pid = 14309;
23

24
SELECT pg_cancel_backend(14274);
25
SELECT pg_terminate_backend(14274);

https://www.alibabacloud.com/help/en/analyticdb-for-postgresql/latest/use-pg-stat-activity-to-analyze-and-diagnose-active-sql-queries

6
1
 pid  |                                query                                 
2
-------+----------------------------------------------------------------------
3
 14309 | alter table features_in_task                                        +
4
       |     add worker_uuid uuid
5
       
6
     16839 | VACUUM full;

Database location

1
1
ps aux | grep postgres | grep -- -D

SQL queries

Arrays and tuples

1
1
WHERE client_user_id = ANY(ARRAY[175, 177, 166, 179])

6
1
cur.execute(
2
"SELECT count(*) FROM conversations "
3
"WHERE client_id in %(clients_ids)s "
4
"AND not deleted",
5
{"clients_ids": tuple(clients_ids)},
6
)

Group by day

3
1
SELECT COUNT(id) FROM conversations
2
WHERE created < now() - INTERVAL '14 day'
3
GROUP BY to_char(created, 'yyyy-mm-dd')

15
1
cursor.execute(
2
                    "SELECT users_voiceid_embeddings.* "
3
                    "FROM users_voiceid_embeddings "
4
                    "INNER JOIN users_voiceids ON users_voiceids.users_voiceid_id = users_voiceid_embeddings.users_voiceid_id "
5
                    "WHERE users_voiceid_embeddings.internal_user_id=%(internal_user_id)s "
6
                    "AND users_voiceid_embeddings.feature_id=%(feature_id)s "
7
                    "AND users_voiceids.voiceid_type = ANY(%(voiceid_type)s) "
8
                    "AND NOT users_voiceid_embeddings.is_deleted "
9
                    "AND NOT users_voiceids.is_deleted",
10
                    {
11
                        "internal_user_id": internal_user_id,
12
                        "feature_id": feature_id,
13
                        "voiceid_type": [str(it) for it in include_voiceid_types]
14
                    }
15
                )

Avoid inserting twice if cannot use unique index (is_deleted case)

Access SQL via console

1
1
sudo -u postgres psql

Rename table

xxxxxxxxxx
1
1
ALTER DATABASE table_name RENAME TO new_table_name;

Dump and restore - SQL

xxxxxxxxxx
4
1
PGPASSWORD="PASS" pg_dump -U asya --dbname=api --file=./dump-2021-12-10.sql --username=api --host=18.221.203.141 --port=543
2

3
psql -U api -d api -W < /home/ubuntu/documents/backups/dump-2021-12-10.sql
4

https://www.postgresql.org/docs/9.3/app-pgdump.html

pg_dump additional useful flags

xxxxxxxxxx
6
1
--schema-only \
2
--column-inserts \
3
--data-only \
4

5
--table=app_strings \
6
--table=app_strings_localized \

Binary dump

xxxxxxxxxx
5
1
PGPASSWORD="PASS" pg_dump -U asya --dbname=api --file=./dump-2021-12-10.dmp --compress=9 --format=c --username=api --host=18.221.203.141 --port=5432
2

3
pg_restore --jobs=8 --dbname=database /home/ubuntu/documents/backups/dump-2021-12-10.dmp
4

5
sudo -u postgres  pg_restore --jobs=8 --dbname=asya /dump-asya.dmp

Delete

xxxxxxxxxx
10
1
ALTER TABLE table_name DISABLE TRIGGER ALL;
2

3

4
-- Delete the rows from the table
5
DELETE FROM table_name WHERE condition;
6

7
-- Re-enable all triggers on the table
8
ALTER TABLE table_name ENABLE TRIGGER ALL;
9
ALTER TABLE tasks ENABLE TRIGGER ALL;
10
;

Login as sudo

xxxxxxxxxx
1
1
sudo -u postgres psql

Switch database

xxxxxxxxxx
1
1
\c database

List constraints and triggers of table

xxxxxxxxxx
1
1
\dS table_name