Postgres Notes

Installing Postgres in Docker

1 2 3 4

docker search postgresql docker run --name postgresqldb -e POSTGRES_USER=myusername -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -v /data:/var/lib/postgresql/data -d postgres docker ps docker start postgresqldb

Connecting through PG Admin tool

Stopping the docker instance

docker stop postgresqldb

Listing the docker images history

docker image ls

Locks on the table

While altering the table if it's taking long time and never completes the operation then check the locks on the table and their activity. Below queries to get the lock info

1	SELECT locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath FROM pg_locks WHERE relation = '<schema>.<table_name>'::regclass::oid;

One row per server process, showing information related to the current activity of that process, such as state and current query. 

 

1	SELECT datid, datname, pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_xid, backend_xmin, query, backend_type FROM pg_stat_activity WHERE pid = <pid>;

Executing the function in postgres

1	SELECT <schema>.<function_name>('argument 1', 'argument 2');

Exporting the table to a csv file

COPY table_name(col_name1,col_name2,col_name3) 
TO 'C:\temp\csv_file_name.csv' DELIMITER '|' CSV HEADER;

Getting the version of the postgres

SELECT version();