pg_dumpall -U postgres > pg_dumpall.db
... dump all databases to pg_dumpall.db
pg_dump -U blackmore blackmore > blackmore_db.db
... dump the whole database 'blackmore' to blackmore_db.db
pg_dump -U blackmore -n music_library blackmore > music_library.db
... dump only the 'music_library' schema from the 'blackmore' database
pg_dump -c -U blackmore -d blackmore -n music_library > music_library.db
... the same is above, only it generates DROP statements to clear the schema
pg_dump -s -U blackmore -t music_library.music_album -d blackmore
... show the definition of the table music_album (similar to SHOW CREATE TABLE in MySQL)
pg_restore -f sql_dump.sql -O -x custom_dump.pgdump
... create a SQL-dump from the custom dump custom_dump.pgdump
SELECT pid, datname, usename, client_addr, application_name, query_start, state FROM pg_stat_activity;
... lists all active connections
SELECT sum(numbackends) FROM pg_stat_database;
... lists amount of connections
SELECT pg_locks.* FROM pg_locks, pg_database WHERE pg_locks.database = pg_database.oid AND pg_database.datname = 'mydb';
... lists all the locks in the database mydb
SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted FROM pg_locks l, pg_stat_all_tables t WHERE l.relation=t.relid ORDER BY relation ASC;
... lists all the locks with another information
SELECT pg_terminate_backend(<pid>);
... terminates a running connection (backend) identified by pid
SELECT schema_name FROM information_schema.schemata;
... lists all schemas (apart from public) in the current database
SELECT n.nspname AS table_schema, c.relname AS table_name
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('information_schema', 'pg_catalog')
AND c.reltuples = 0
ORDER BY table_schema, table_name;
... lists empty tables
SELECT c.table_schema AS t_schema, c.table_name AS t_name
FROM information_schema.columns c
WHERE column_name = 'field_name' AND c.table_schema IN ('fc_consumption', 'fc_features', 'fc_imbalance')
ORDER BY 1, 2
... lists all tables that contain the column field_name in the given schemas
SELECT
schema_name,
relname,
size_bytes,
pg_size_pretty(size_bytes) AS size
FROM (
SELECT
pg_catalog.pg_namespace.nspname AS schema_name,
relname,
pg_relation_size(pg_catalog.pg_class.oid) AS size_bytes
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
WHERE schema_name IN ('foo', 'bar') AND size_bytes > 0
ORDER BY size_bytes DESC;
... lists schema, table-name, human readable size and size in bytes for all entities in the current database within the schemas 'foo' and 'bar' ordered by size in bytes
SELECT
table_schema,
table_name,
pg_relation_size(table_schema||'.'||table_name) AS size_bytes,
pg_size_pretty(pg_relation_size(table_schema||'.'||table_name)) AS size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size_bytes DESC;
... lists schema, table-name, size in bytes and human readable size for all tables in the current database not belonging to the 'information_schema', 'pg_catalog' schemas
CREATE TABLE economy_accountsfiles (id SERIAL NOT NULL PRIMARY KEY, accounts_id INTEGER NOT NULL REFERENCES economy_accounts(id), file_id INTEGER NOT NULL REFERENCES file(id));
... creates a simple many-to-many table
INSERT INTO economy_accountsfiles (accounts_id, file_id) SELECT DISTINCT accounts_id, file_id FROM economy_accountsline WHERE file_id IS NOT NULL;
... inserts the values returned by a SELECT statement
SELECT cname FROM (VALUES ('foo'), ('bar')) AS s(cname);
... returns the rows 'foo' and 'bar' under column name 'cname'
COPY (
SELECT column1, column2, COUNT(*) AS duplicate_count
FROM mytable
GROUP BY column1, column2
HAVING COUNT(*) > 1
ORDER BY column1, column2)
TO '/tmp/data.csv' WITH CSV DELIMITER ';' HEADER;
... deals with duplicates and saves to the output to a .csv file
2026-04-11 10:40:36
minicms - © 2020-2026 Simeon Simeonov