psql -h postgres01.oric.no -U mysuperuser postgres -c "COPY (SELECT name || '=' || setting FROM pg_settings ORDER BY name) TO STDOUT WITH CSV HEADER" > pg_config.csv
Minimalistic Oracle
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Thursday, August 28, 2025
Spool out all parameters from a datafile to a file
Wednesday, August 27, 2025
List a table, its partitions and the number of rows it contains
SELECT s.schemaname, s.relname AS table_name, s.n_live_tup, s.last_analyze, s.last_autoanalyze FROM pg_stat_all_tables s JOIN pg_class c ON c.relname = s.relname LEFT JOIN pg_inherits i ON i.inhrelid = c.oid OR i.inhparent = c.oid WHERE s.schemaname = 'myschema' AND ( s.relname = 'mytable' OR c.oid IN ( SELECT inhrelid FROM pg_inherits WHERE inhparent = ( SELECT oid FROM pg_class WHERE relname = 'mytable' ) ) ) ORDER BY s.n_live_tup DESC,s.last_analyze;Example output
schemaname | table_name | n_live_tup | last_analyze | last_autoanalyze -----------------+-----------------+------------+-------------------------------+------------------------------ myschema | mytable_2015_02 | 95788115 | 2025-08-06 12:58:22.952592+00 | myschema | mytable_2015_03 | 78505350 | 2025-08-06 12:58:37.147433+00 | myschema | mytable_2015_04 | 71211253 | 2025-08-06 12:58:51.311452+00 | myschema | mytable_2015_01 | 68255510 | 2025-08-06 12:58:08.42708+00 | myschema | mytable_2015_05 | 62075043 | 2025-08-06 12:59:05.434118+00 | myschema | mytable_2015_06 | 60888876 | 2025-08-06 12:59:19.918657+00 | 2025-08-06 11:31:19.551345+00 myschema | mytable_2015_12 | 57485594 | 2025-08-06 13:00:43.112316+00 | 2025-08-27 03:13:11.394606+00 myschema | mytable_2016_01 | 57405307 | 2025-08-06 13:00:55.395113+00 | 2025-08-25 13:35:07.749773+00
Thursday, July 31, 2025
How to check if a PostgreSQL streaming replicate server is up-to-date with the primary
On the primary, run
This means that
All WAL records have been received
All WAL recores have been replayed (not just flushed) on the standby
There is no lag between primary and standby
SELECT pg_current_wal_lsn(); -- On the primaryIn my case, the result was
pg_current_wal_lsn -------------------- 0/E015420On the standby, run
SELECT pg_last_wal_replay_lsn();In my case, the result was
pg_last_wal_replay_lsn ------------------------ 0/E015420
This means that
Basic postgres connect syntax
psql -h hostname -U username -d databaseOptions:
Option Description -h <host> Hostname or IP address of the PostgreSQL server -p <port> Port number (default is 5432) -U <username> Username to connect as -d <dbname> Database name -W Prompt for password (useful if password auth is required) -f <filename> Execute commands from a SQL script file -c "<command>" Execute a single SQL command and exit -v var=value Set psql variable (used inside scripts) --set=var=value Same as -v, more explicit --dbname=<connstr> Full connection string, e.g.: postgresql://user:pass@host:port/dbnameExample
psql -h pg01.oric.no -p 5432 -U myuser -d mydbAlternatively, use an URL, which is more scripting friendly:
psql "postgresql://replicate@pg01.oric.no:5432/mydb"
Wednesday, July 30, 2025
How to find the columns in a specific postgres data dictionary table
For example, to check which columns belongs to the view "pg_stat_wal_receiver"
SELECT column_name FROM information_schema.columns WHERE table_name = 'pg_stat_wal_receiver';Output
column_name ----------------------- pid last_msg_receipt_time latest_end_lsn latest_end_time sender_port receive_start_lsn receive_start_tli written_lsn flushed_lsn received_tli last_msg_send_time status slot_name sender_host conninfo (15 rows)Or use the psql metacommand "\d+":
echo '\d+ pg_stat_wal_receiver' |psqlOutput
View "pg_catalog.pg_stat_wal_receiver" Column | Type | Collation | Nullable | Default | Storage | Description -----------------------+--------------------------+-----------+----------+---------+----------+------------- pid | integer | | | | plain | status | text | | | | extended | receive_start_lsn | pg_lsn | | | | plain | receive_start_tli | integer | | | | plain | written_lsn | pg_lsn | | | | plain | flushed_lsn | pg_lsn | | | | plain | received_tli | integer | | | | plain | last_msg_send_time | timestamp with time zone | | | | plain | last_msg_receipt_time | timestamp with time zone | | | | plain | latest_end_lsn | pg_lsn | | | | plain | latest_end_time | timestamp with time zone | | | | plain | slot_name | text | | | | extended | sender_host | text | | | | extended | sender_port | integer | | | | plain | conninfo | text | | | | extended | View definition: SELECT pid, status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port, conninfo FROM pg_stat_get_wal_receiver() s(pid, status, receive_start_lsn, receive_start_tli, written_lsn, flushed_lsn, received_tli, last_msg_send_time, last_msg_receipt_time, latest_end_lsn, latest_end_time, slot_name, sender_host, sender_port, conninfo) WHERE pid IS NOT NULL;
Monday, July 28, 2025
How to list the postgres processes running on your server and what their jobs are
A neet little trick I learned from the series "Mirage of memory, part 2: PSS"
You could also count the number of processes by user "postgres" by simply typing
ps -fu postgres | grep $(pgrep -f postmaster) postgres 1913 1 0 Jul17 ? 00:02:51 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/ postgres 1982 1913 0 Jul17 ? 00:00:24 postgres: logger postgres 2063 1913 0 Jul17 ? 00:00:18 postgres: checkpointer postgres 2064 1913 0 Jul17 ? 00:00:03 postgres: background writer postgres 2119 1913 0 Jul17 ? 00:00:07 postgres: walwriter postgres 2120 1913 0 Jul17 ? 00:00:14 postgres: autovacuum launcher postgres 2121 1913 0 Jul17 ? 00:00:01 postgres: archiver postgres 2122 1913 0 Jul17 ? 00:00:00 postgres: logical replication launcher postgres 2657 1913 0 Jul17 ? 00:02:05 postgres: postgres_exporter postgres localhost(48674) idleThe command lists all processes by user "postgres" and and greps for the output of the command "pgrep -f postmaster", which returns a list of process IDs
You could also count the number of processes by user "postgres" by simply typing
pgrep -c postmaster 9
Monday, July 14, 2025
How to find when a PostgreSQL table was last vacuumed
SELECT schemaname, relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'documents'; schemaname | relname | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze ----------------+----------+-------------+-------------------------------+-------------------------------+------------------------------- scott | documents| | 2025-06-25 02:05:20.692869+02 | 2025-07-12 22:04:05.119051+02 | 2025-06-17 08:41:12.567351+02 (1 row)Explanation of columns:
What does ANALYZE do in PostgreSQL?
ANALYZE collects statistics about the contents of a table (or all tables), such as:
Syntax to run ANALYZE
Analyze the entire database (all tables in all schemas):
ANALYZE;Analyze a specific table:
ANALYZE my_table;Analyze specific columns only:
ANALYZE my_table (column1, column2);
Subscribe to:
Posts (Atom)