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