Showing posts with label Postgres. Show all posts
Showing posts with label Postgres. Show all posts
Monday, September 8, 2025

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; ...
Friday, September 5, 2025

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, ...
Thursday, August 28, 2025

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...
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_ta...
Thursday, July 31, 2025

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 -------------------- ...

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...
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.colu...
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 " ps -fu postgres | grep $(pgrep -f postmaster) p...
1 comment:
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 = 'docu...
Friday, July 11, 2025

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_sta...
Thursday, June 19, 2025

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.m...
Thursday, June 5, 2025

PostgreSQL search_path basics

›
Similar to Oracles alter session set current_schema=<schema_name> , in PostgreSQL, search_path is a session-level setting that dete...

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 DATABA...

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 ...
Tuesday, June 3, 2025

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 o...

How to setup huge pages on a postgreSQL server

›
The Postgres Documentation states: The use of huge pages results in smaller page tables and less CPU time spent on memory management, inc...
Friday, May 23, 2025

How to find constraints on a table in postgres

›
SELECT con.conname AS constraint_name, CASE con.contype WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u...
Thursday, May 22, 2025

PostgreSQL: difference between \copy and COPY

›
If you put a statement like this in a file: COPY at_locations FROM '/external/data/geolocations_at_fixed.csv' WITH ( FORMAT csv...
Wednesday, April 9, 2025

How to mimic sqlplus variable behaviour in PostgreSQL

›
Here is how you can mimic the usage of variables at the prompt using psql and PostgreSQL like you would with oracle and sqlplus/sqlc: \conn...
Wednesday, April 2, 2025

How to check the number of rows inserted into the different partitions in a PostgreSQL partitioned table

›
Create the table: CREATE TABLE mytable ( id bigserial, key text COLLATE pg_catalog."default" NOT NULL, outdata jsonb...