How to pass parameters to a psql script

To use variables in your sql script, use the -v option to set variables on the command line.

Consider this example:

export PGPASSWORD=mysecretpassword
psql -h myserver.oric.no -d db01 -U scott -v schema=scott -v table=mytable -f myquery.sql

The query in myquery.sql can then referance the "schema" and "table" variables, like this:

SELECT
    att.attname AS column_name
FROM
    pg_attribute att
JOIN
    pg_class cls ON cls.oid = att.attrelid
JOIN
    pg_namespace ns ON ns.oid = cls.relnamespace
WHERE
    ns.nspname = :'schema'
    AND cls.relname = :'table'
    AND att.attnotnull
    AND att.attnum > 0
    AND NOT att.attisdropped;

Find out if a constraint in PostgreSQL is defined as deferrable

Logged into the relevant database, find out which FK are deferrable:

SELECT conname, condeferrable, condeferred
FROM pg_constraint
WHERE conrelid in ( 'mytable1'::regclass,'mytable2'::regclass) 
AND contype='f';
           conname            | condeferrable | condeferred
------------------------------+---------------+-------------
 mytable1_id_fkey             | f             | f
 mytable1_id_fkey             | f             | f
 mytable2_id_fkey             | f             | f

If condeferrable = t (true), the constraint is created initially deferrable

If condeferrable = f (false), like in my case, it is not.

Terminate PostgresSQL sessions

-- Terminate all connections from user "sales"
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'sales';

In my case the result was:

 pg_terminate_backend
----------------------
 t
 t
(2 rows)

Check active sessions in PostgreSQL

The closest equivalent to this oracle query:

SELECT USERNAME, COUNT(*) 
FROM v$session 
WHERE type <> 'BACKGROUND' 
GROUP BY username;

Would be

SELECT datname, usename AS username, COUNT(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename
ORDER BY datname, usename;

Example output:

 datname  |     username      | count
----------+-------------------+-------
 postgres | postgres          |     1
 postgres | postgres_exporter |     1
 mydb     | myser             |     2
(3 rows)

How to set echo in a SQL script executed in psql

Use one of two options 1. in the sql file, add the following directive

\set ECHO queries

2. execute the script using the -e flag

psql -e -f myfile.sql

Syntax for drop/recreate of constraints in PostgreSQL

1. Find the constraints belonging to a specific table:

SELECT conname
FROM pg_constraint
WHERE conrelid = 'mytable'::regclass;
    conname
----------------
 sys_c002733781
 sys_c002733782
(2 rows)

2. Drop them:

ALTER TABLE mytable DROP CONSTRAINT sys_c002733781;
ALTER TABLE
ALTER TABLE mytable DROP CONSTRAINT sys_c002733782;
ALTER TABLE

3. Load your data using your preferred method

4. Recreate the FK constraints but skip validating existing data (only new inserts/updates are checked):

alter table mytable add constraint sys_c002733781 foreign key(job_id) references jobs(id) on delete cascade not valid;
ALTER TABLE

alter table mytable add constraint sys_c002733782 foreign key(task_id) references tasks(id) on delete cascade not valid;
ALTER TABLE

5. Validate constraints:

alter table mytable validate constraint sys_c002733781;
ALTER TABLE
alter table mytable validate constraint sys_c002733782;
ALTER TABLE

Query equivalent to SELECT * FROM USER_OBJECTS in PostgreSQL

SELECT schema_name, object_name, object_type
FROM (
    -- Tables, Views, Indexes, etc.
    SELECT
        n.nspname AS schema_name,
        c.relname AS object_name,
        CASE c.relkind
            WHEN 'r' THEN 'TABLE'
            WHEN 'v' THEN 'VIEW'
            WHEN 'm' THEN 'MATERIALIZED VIEW'
            WHEN 'i' THEN 'INDEX'
            WHEN 'S' THEN 'SEQUENCE'
            WHEN 'f' THEN 'FOREIGN TABLE'
            WHEN 'p' THEN 'PARTITIONED TABLE'
            WHEN 'I' THEN 'PARTITIONED INDEX'
            ELSE c.relkind::text
        END AS object_type
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = current_schema()

    UNION ALL

    -- Functions
    SELECT
        n.nspname AS schema_name,
        p.proname AS object_name,
        'FUNCTION' AS object_type
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = current_schema()
) AS objects
ORDER BY object_type, object_name;

It only shows objects in the current schema (like Oracle USER_OBJECTS).

If you want all objects the user owns (across schemas), replace

WHERE n.nspname = current_schema()

with

WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')

Spool out all parameters from a datafile to a file

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

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

How to check if a PostgreSQL streaming replicate server is up-to-date with the primary

On the primary, run

SELECT pg_current_wal_lsn(); -- On the primary

In my case, the result was

 pg_current_wal_lsn
--------------------
 0/E015420

On 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

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

Basic postgres connect syntax

psql -h hostname -U username -d database

Options:

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/dbname

Example

psql -h pg01.oric.no -p 5432 -U myuser -d mydb

Alternatively, use an URL, which is more scripting friendly:

psql "postgresql://replicate@pg01.oric.no:5432/mydb"

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' |psql

Output

                                  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;

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"

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) idle

The 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

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:

last_vacuum: Last manual vacuum.

last_autovacuum: Last automatic vacuum.

last_analyze: Last manual analyze.

last_autoanalyze: Last automatic analyze.

What does ANALYZE do in PostgreSQL?

ANALYZE collects statistics about the contents of a table (or all tables), such as:

Number of rows

Distribution of values

Most common values

Histogram of value ranges

Null fractions These statistics are stored in pg_statistic and are used by the query planner to make decisions (e.g., which index to use, join strategies, etc.).

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);

PostgreSQL: how to tail the alert log of the day with filtering for long-running statements

I have set the parameter log_min_duration_statement to 2 seconds and reloaded the config:

postgres=# alter system set log_min_duration_statement='2s';
ALTER SYSTEM

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 2s
(1 row)

I expect every statement that lasts longer than 2 seconds to be logged in the postgreSQL server main log.

From a different session, I execute the following in any database in the cluster:

postgres=# SELECT pg_sleep(3);
 pg_sleep
----------

(1 row)

From another session, tail the log from today:

tail -F /var/log/postgres/postgresql-2025-07-11_000000.log | grep --line-buffered "duration:.*statement:"

Output

2025-07-11 09:45:42 CEST [173349]: [5-1] user=postgres,db=postgres,app=psql,client=[local] LOG:  duration: 3003.698 ms  statement: SELECT pg_sleep(3);

Postgres: List all RANGE partitions, sorted by their range start value

My table contains 180 partitons, and looks like this when described in psql:

mydb=> \d mytable
      Partitioned table "myschema.mytable"
      Column       |           Type           | Collation | Nullable |           Default            
-------------------+--------------------------+-----------+----------+------------------------------
 id                | bigint                   |           | not null | generated always as identity
 ad_updtud_log_id  | bigint                   |           | not null | 
 period            | integer                  |           | not null | 
 created           | timestamp with time zone |           | not null | 
Partition key: RANGE (id)
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id, periode)
    "mytable_id_index" btree (im_oppsum_logg_id)
    "mytable_opprettet_index" btree (opprettet)
Foreign-key constraints:
    "fk_mytable" FOREIGN KEY (ad_updtud_log_id, period) REFERENCES mytable2(id, period)
Number of partitions: 180 (Use \d+ to list them.)

This is how you can list all partitions belonging to a range-partitioned table in postgreSQL, based on the values:

SELECT
    child.relname AS partition_name,
    regexp_replace(pg_get_expr(child.relpartbound, child.oid), '.*FROM \(''(\d+).*', '\1')::bigint AS range_start,
    pg_get_expr(child.relpartbound, child.oid) AS partition_range
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nm ON nm.oid = child.relnamespace
WHERE parent.relname = 'mytable'
  AND nm.nspname = 'myschema'
ORDER BY range_start;

Result (excerpt only):

   partition_name   | range_start | partition_range                  
---------------------------------------+-------------+--------------------------------------------
 mytable_0	    |           0 | FOR VALUES FROM ('0') TO ('30000000')
 mytable_30000000   |    30000000 | FOR VALUES FROM ('30000000') TO ('60000000')
 mytable_60000000   |    60000000 | FOR VALUES FROM ('60000000') TO ('90000000')
 mytable_90000000   |    90000000 | FOR VALUES FROM ('90000000') TO ('120000000')
 mytable_120000000  |   120000000 | FOR VALUES FROM ('120000000') TO ('150000000')
 mytable_150000000  |   150000000 | FOR VALUES FROM ('150000000') TO ('180000000')
 mytable_180000000  |   180000000 | FOR VALUES FROM ('180000000') TO ('210000000')
 mytable_210000000  |   210000000 | FOR VALUES FROM ('210000000') TO ('240000000')
 mytable_240000000  |   240000000 | FOR VALUES FROM ('240000000') TO ('270000000')
 mytable_270000000  |   270000000 | FOR VALUES FROM ('270000000') TO ('300000000')
 mytable_300000000  |   300000000 | FOR VALUES FROM ('300000000') TO ('330000000')
 mytable_330000000  |   330000000 | FOR VALUES FROM ('330000000') TO ('360000000')
 mytable_360000000  |   360000000 | FOR VALUES FROM ('360000000') TO ('390000000')
 mytable_390000000  |   390000000 | FOR VALUES FROM ('390000000') TO ('420000000')
 mytable_420000000  |   420000000 | FOR VALUES FROM ('420000000') TO ('450000000')
 mytable_450000000  |   450000000 | FOR VALUES FROM ('450000000') TO ('480000000')
 

PostgreSQL search_path basics

Similar to Oracles alter session set current_schema=<schema_name>, in PostgreSQL, search_path is a session-level setting that determines the order in which schemas are searched when you reference database objects without a schema name. It consists of a list of schema names. When you run a query like

SELECT * FROM mytable;

PostgreSQL checks each schema in the list — in order — to find mytable.

Default value is:

 "$user", public 

This means:

1. Look for a schema named after the current user.
2. If not found or not accessible, try the public schema.

How to view and set it

Check your current search_path:

 SHOW search_path; 

Set it for the session:

 SET search_path TO schema1, public; 

or

 SET search_path TO schema1, schema2, public; 

The last example is important in cases where you logon to a database with a user with no matching schema. Consider the following example:

 psql -h server1.oric.no -U jim -d musicdb

I am logging onto the database "musicdb" with a user called "jim". By default, jim will have its own user followed by public, in his search path:

musicdb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

I have already given user jim the privilges needed to "see" the objects created by schema "music" which exists in the database "musicdb".

For convenience, add schema "music" to the search_path:

musicdb=> set search_path to 'music','$user','public';
SET
musicdb=> show search_path;
          search_path
-------------------------------
 music, "$user", public
(1 row)

The "current_user" and "current_schema" functions will now return the actual user name, and the first match in the search_path, respectivly:

musicdb=> select current_user, current_schema;
 current_user | current_schema
--------------+----------------
 jim          | music
(1 row)

Why is it important?
It controls where PostgreSQL looks first for unqualified object names. It allows you to skip schema prefixes when working with other schemas' objects

How to limit connections in a postgres database

This is how you can restrict connections for a specific database to zero:

postgres=# alter database db01 connection limit 0;
ALTER DATABASE

Verify

SELECT datname, datconnlimit FROM pg_database WHERE datname = 'db01';
-[ RECORD 1 ]+-------------
datname      | db01
datconnlimit | 0

Set back to unlimited:

ALTER DATABASE db01 CONNECTION LIMIT -1;
ALTER DATABASE

Verify:

SELECT datname, datconnlimit FROM pg_database WHERE datname = 'db01';
-[ RECORD 1 ]+-------------
datname      | db01
datconnlimit | -1

To limit the connections for a specific user only:

 psql
psql (15.13)
Type "help" for help.

postgres=# alter user read_db01 connection limit 0;
ALTER ROLE
postgres=# alter user read_db01 connection limit -1;
ALTER ROLE
postgres=#

The current setting can be verified with:

 SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolname = 'read_db01';
      rolname      | rolconnlimit
-------------------+--------------
 read_db01         |           -1

or, list all users that does have restrictions on the number of connections:

 SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit != -1;
      rolname      | rolconnlimit
-------------------+--------------
 pganalyze         |            5
 read_db01         |            0
(2 rows)

create user in postgres - basic syntax

I will create

a group role called common_users

a user called ro_user1

in a database called db01

The read-only user ro_user1 should be able perform queries against all tables owned by the schema schema1.

First, create the role common_users by logging onto the postgres (default) database

psql

CREATE ROLE common_users WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS;

GRANT pg_read_all_stats TO common_users;

Then, create the user ro_user1:

create user ro_user1 password 'mysecretpassword';
grant common_users to ro_user1;
grant connect on database db01 to ro_user1;

Log into the database db01 and revoke and grant some privileges:

psql
\connect db01
revoke all on schema schema1 from ro_user1;
grant usage on schema schema1 to ro_user1;
grant select on all tables in schema schema1 to ro_user1;

Confirm the privileges:

\connect postgres
select database_privs('ro_user1');

PostgreSQL Memory Parameters and how they relate

Here are some of the important memory parameters in a postgreSQL server, and how they relate to one another.

shared_buffers

The amount of memory the database server uses for shared memory buffers.

The postgres documentation suggest starting with allocating 25% of the available memory to the shared database memory pool:

If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system... because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount.

It also points out the necessity of considering database checkpointing:

Larger settings for shared_buffers usually require a corresponding increase in max_wal_size, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.

max_wal_size

Controls the maximum size the Write-Ahead Logging (WAL) files can grow before triggering a checkpoint. Checkponts are relative expensive operations, so we do not want them to occur too often. On the other hands, too infrequent checkpointing may increase recovery times. max_wal_size can be set to balance performance and recovery time by influencing how often checkpoints occur.

work_mem

Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.

The documentation points out that a complex query might perform several sort and hash operations at the same time, with each operation generally being allowed to use as much memory as this value specifies before it starts to write data into temporary files and that serveral running sessions could be executing such operations at the same time. So even if the 6M specified as its value does not seem like much, it could mean significant memory usage on a busy system.

It is similar to pga_aggregate_target in an oracle database: the amount of memory set for all private global areas on the server. After the introduction of this parameter in Oracle 9i, the private global area parameters used back then, for example sort_area_size and hash_area_size, was not necessary any longer.

maintenance_work_mem

Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

It can be set higher than work_mem: Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.

autovacuum_work_mem

Specifies the maximum amount of memory to be used by each autovacuum worker process. If this value is specified without units, it is taken as kilobytes. It defaults to -1, indicating that the value of maintenance_work_mem should be used instead. The setting has no effect on the behavior of VACUUM when run in other contexts.

Here is a table with my settings for my 16G Memory server

Parameter	Value
shared_buffers	4GB
max_wal_size	8GB
work_mem	6MB
maintenance_work_mem	479MB

Source: PostgreSQL documentation