Postgres: Difference between revisions

From I Will Fear No Evil
Jump to navigation Jump to search
(Created page with "== 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) <pre> sudo -u postgres psql postgres </pre> Create User <pre> CREATE ROLE someUser LOGIN PASSWORD 'somePassword'; </pre> Create database and add someUser as the owner <pre> CREATE DATABASE databaseName with owner =...")
 
mNo edit summary
 
(4 intermediate revisions by the same user not shown)
Line 28: Line 28:
* \dt  display tables
* \dt  display tables
* \l  list databases
* \l  list databases
=== Migrate from MySQL to Psql ===
install pgloader application
Edit script.lisp
<pre>
/* content of the script.lisp */
LOAD DATABASE
FROM mysql://mysqlUser:mysqlPassword@localhost|IP/oldDatabaseName
INTO postgresql://someUser:somePassword@localhost/databaseName;
</pre>
Run: pgloader script.lisp
<pre>
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
</pre>
=== Dump a Postgres database ===
To dump just the schema and indexes
<pre>
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>


=== Links ===
=== Links ===
[https://stackoverflow.com/questions/2172569/how-to-login-and-authenticate-to-postgresql-after-a-fresh-install| Stack Overflow fresh install notes]
[https://stackoverflow.com/questions/2172569/how-to-login-and-authenticate-to-postgresql-after-a-fresh-install| Stack Overflow fresh install notes]


[https://serverfault.com/questions/198002/postgresql-what-does-grant-all-privileges-on-database-do| Server Fault Grants and permissions information]




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

Links

Stack Overflow fresh install notes

Server Fault Grants and permissions information

Retrieved from "https://wiki.iwillfearnoevil.com/mediawiki/index.php?title=Postgres&oldid=649"