Postgres

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 

Links

Stack Overflow fresh install notes

Server Fault Grants and permissions information