Postgres: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 75: | Line 75: | ||
<pre> | <pre> | ||
pg_dump -s databaseName -U someUser -h localhost > databaseName_database_schema_postgres.sql | pg_dump -s databaseName -U someUser -h localhost > databaseName_database_schema_postgres.sql | ||
</pre> | |||
=== Find active queries === | |||
See what is actually happening at the time of a query | |||
[https://stackoverflow.com/questions/27435839/how-to-list-active-connections-on-postgresql List active connections on postgresql] | |||
<pre> | |||
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 | |||
</pre> | </pre> | ||
Latest revision as of 09:29, 10 April 2025
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