Postgres
Jump to navigation
Jump to search
Postgres Notes
I generally use MySQL, however there have been cases where I need to use Postgres. I can never remember the exact syntax to do basic things, so here we are...
Create User and Database
Admin login to Postgeres (fresh install Pg14)
sudo -u postgres psql postgres
Create User
CREATE ROLE someUser LOGIN PASSWORD 'somePassword';
Create database and add someUser as the owner
CREATE DATABASE databaseName with owner = someUser;
Validate that this worked
psql -h localhost -d databaseName -U someUser -p 5432 Password for user someUser: somePassword psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help.
Useful commands to remember
- \q quit
- \dt display tables
- \l list databases
Migrate from MySQL to Psql
install pgloader application
Edit script.lisp
/* content of the script.lisp */ LOAD DATABASE FROM mysql://mysqlUser:mysqlPassword@localhost|IP/oldDatabaseName INTO postgresql://someUser:somePassword@localhost/databaseName;
Run: pgloader script.lisp
2024-04-03T18:56:34.028000Z LOG pgloader version "3.6.3~devel"
2024-04-03T18:56:34.036000Z LOG Data errors in '/tmp/pgloader/'
2024-04-03T18:56:34.036000Z LOG Parsing commands from file #P"/home/chubbard/script.lisp"
2024-04-03T18:56:34.268005Z LOG Migrating from #<MYSQL-CONNECTION mysql://mysqlUser@192.168.15.250:3306/oldDatabaseName {10080B3B03}>
2024-04-03T18:56:34.272005Z LOG Migrating into #<PGSQL-CONNECTION pgsql://someUser@localhost:5432/databaseName {10080B4783}>
2024-04-03T18:56:36.244042Z LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 3 1.204s
Create Schemas 0 0 0.024s
Create SQL Types 0 0 0.024s
Create tables 0 2 0.076s
Set Table OIDs 0 1 0.020s
----------------------- --------- --------- --------- --------------
databaseName.state 0 8 0.7 kB 0.084s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.092s
Index Build Completion 0 2 0.080s
Create Indexes 0 2 0.016s
Reset Sequences 0 1 0.092s
Primary Keys 0 1 0.004s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Set Search Path 0 1 0.000s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 8 0.7 kB 0.284s
Dump a Postgres database
To dump just the schema and indexes
pg_dump -s databaseName -U someUser -h localhost > databaseName_database_schema_postgres.sql
Find active queries
See what is actually happening at the time of a query List active connections on postgresql
someDb=# SELECT
someDb-# pid
someDb-# ,datname
someDb-# ,usename
someDb-# ,application_name
someDb-# ,client_hostname
someDb-# ,client_port
someDb-# ,backend_start
someDb-# ,query_start
someDb-# ,query
someDb-# ,state
someDb-# FROM pg_stat_activity
someDb-# WHERE state = 'active';
pid | datname | usename | application_name | client_hostname | client_port | backend_start | query_start | query | state
------+---------+---------+------------------+-----------------+-------------+-------------------------------+-------------------------------+-----------------------------------------+--------
9132 | someDb | someDb | psql | | -1 | 2025-04-10 16:23:07.854726+00 | 2025-04-10 16:25:08.334369+00 | SELECT +| active
| | | | | | | | pid +|
| | | | | | | | ,datname +|
| | | | | | | | ,usename +|
| | | | | | | | ,application_name +|
| | | | | | | | ,client_hostname +|
| | | | | | | | ,client_port +|
| | | | | | | | ,backend_start +|
| | | | | | | | ,query_start +|
| | | | | | | | ,query +|
| | | | | | | | ,state +|
| | | | | | | | FROM pg_stat_activity +|
| | | | | | | | WHERE state = 'active'; |
5416 | | someDb | walreceiver | | 57680 | 2025-04-10 15:38:21.861295+00 | 2025-04-10 15:38:21.874055+00 | START_REPLICATION A/E2000000 TIMELINE 1 | active