PostgreSQL: Notes

"PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance."

Download link: https://www.postgresql.org/download/

Fedora Workstation 32

sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-32-x86_64/pgdg-fedora-repo-latest.noarch.rpm
sudo dnf install postgresql12-server postgresql12
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl start postgresql-12
sudo systemctl status postgresql-12
sudo systemctl enable postgresql-12


[tuyen@g73jh ~]$ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-32-x86_64/pgdg-fedora-repo-latest.noarch.rpm
[sudo] password for tuyen:
Last metadata expiration check: 2:25:57 ago on Sat 03 Oct 2020 06:31:37 AM +07.
pgdg-fedora-repo-latest.noarch.rpm                                                      5.3 kB/s |  10 kB     00:01
Dependencies resolved.
========================================================================================================================
 Package                           Architecture            Version                  Repository                     Size
========================================================================================================================
Installing:
 pgdg-fedora-repo                  noarch                  42.0-13                  @commandline                   10 k

Transaction Summary
========================================================================================================================
Install  1 Package

Total size: 10 k
Installed size: 11 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                1/1
  Installing       : pgdg-fedora-repo-42.0-13.noarch                                                                1/1
  Verifying        : pgdg-fedora-repo-42.0-13.noarch                                                                1/1

Installed:
  pgdg-fedora-repo-42.0-13.noarch

Complete!
[tuyen@g73jh ~]$ ls /etc/yum.repos.d/p*
/etc/yum.repos.d/pgdg-fedora-all.repo
[tuyen@g73jh ~]$

[tuyen@g73jh ~]$ sudo dnf install postgresql12-server postgresql12
PostgreSQL common RPMs for Fedora 32 - x86_64                                            69 kB/s | 287 kB     00:04
PostgreSQL 13 for Fedora 32 - x86_64                                                     23 kB/s |  93 kB     00:04
PostgreSQL 12 for Fedora 32 - x86_64                                                     83 kB/s | 275 kB     00:03
PostgreSQL 11 for Fedora 32 - x86_64                                                     43 kB/s | 296 kB     00:06
PostgreSQL 10 for Fedora 32 - x86_64                                                     56 kB/s | 204 kB     00:03
PostgreSQL 9.6 for Fedora 32 - x86_64                                                    43 kB/s | 196 kB     00:04
PostgreSQL 9.5 for Fedora 32 - x86_64                                                    57 kB/s | 181 kB     00:03
Dependencies resolved.
========================================================================================================================
 Package                             Architecture           Version                        Repository              Size
========================================================================================================================
Installing:
 postgresql12-server                 x86_64                 12.4-1PGDG.f32                 pgdg12                 5.2 M
Installing dependencies:
 postgresql12                        x86_64                 12.4-1PGDG.f32                 pgdg12                 1.6 M
 postgresql12-libs                   x86_64                 12.4-1PGDG.f32                 pgdg12                 423 k

Transaction Summary
========================================================================================================================
Install  3 Packages

Total download size: 7.2 M
Installed size: 31 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): postgresql12-libs-12.4-1PGDG.f32.x86_64.rpm                                      260 kB/s | 423 kB     00:01
(2/3): postgresql12-12.4-1PGDG.f32.x86_64.rpm                                           783 kB/s | 1.6 MB     00:02
(3/3): postgresql12-server-12.4-1PGDG.f32.x86_64.rpm                                    1.1 MB/s | 5.2 MB     00:04
------------------------------------------------------------------------------------------------------------------------
Total                                                                                   1.5 MB/s | 7.2 MB     00:04
warning: /var/cache/dnf/pgdg12-0eb709f4ebc48030/packages/postgresql12-12.4-1PGDG.f32.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
PostgreSQL 12 for Fedora 32 - x86_64                                                    1.6 MB/s | 1.7 kB     00:00
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project "
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                1/1
  Installing       : postgresql12-libs-12.4-1PGDG.f32.x86_64                                                        1/3
  Running scriptlet: postgresql12-libs-12.4-1PGDG.f32.x86_64                                                        1/3
  Installing       : postgresql12-12.4-1PGDG.f32.x86_64                                                             2/3
  Running scriptlet: postgresql12-12.4-1PGDG.f32.x86_64                                                             2/3
  Running scriptlet: postgresql12-server-12.4-1PGDG.f32.x86_64                                                      3/3
  Installing       : postgresql12-server-12.4-1PGDG.f32.x86_64                                                      3/3
  Running scriptlet: postgresql12-server-12.4-1PGDG.f32.x86_64                                                      3/3
  Verifying        : postgresql12-12.4-1PGDG.f32.x86_64                                                             1/3
  Verifying        : postgresql12-libs-12.4-1PGDG.f32.x86_64                                                        2/3
  Verifying        : postgresql12-server-12.4-1PGDG.f32.x86_64                                                      3/3

Installed:
  postgresql12-12.4-1PGDG.f32.x86_64 postgresql12-libs-12.4-1PGDG.f32.x86_64 postgresql12-server-12.4-1PGDG.f32.x86_64

Complete!
tuyen@g73jh ~]$ sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

[tuyen@g73jh ~]$ sudo systemctl start postgresql-12
[tuyen@g73jh ~]$ sudo systemctl status postgresql-12
● postgresql-12.service - PostgreSQL 12 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; disabled; vendor preset: disabled)
     Active: active (running) since Sat 2020-10-03 09:03:50 +07; 2s ago
       Docs: https://www.postgresql.org/docs/12/static/
    Process: 525262 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 525267 (postmaster)
      Tasks: 8 (limit: 16640)
     Memory: 16.7M
     CGroup: /system.slice/postgresql-12.service
             ├─525267 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
             ├─525268 postgres: logger
             ├─525270 postgres: checkpointer
             ├─525271 postgres: background writer
             ├─525272 postgres: walwriter
             ├─525273 postgres: autovacuum launcher
             ├─525274 postgres: stats collector
             └─525275 postgres: logical replication launcher

Oct 03 09:03:50 g73jh systemd[1]: Starting PostgreSQL 12 database server...
Oct 03 09:03:50 g73jh postmaster[525267]: 2020-10-03 09:03:50.451 +07 [525267] LOG:  starting PostgreSQL 12.4 on x86_64>
Oct 03 09:03:50 g73jh postmaster[525267]: 2020-10-03 09:03:50.459 +07 [525267] LOG:  listening on IPv4 address "127.0.0>
Oct 03 09:03:50 g73jh postmaster[525267]: 2020-10-03 09:03:50.464 +07 [525267] LOG:  listening on Unix socket "/var/run>
Oct 03 09:03:50 g73jh postmaster[525267]: 2020-10-03 09:03:50.468 +07 [525267] LOG:  listening on Unix socket "/tmp/.s.>
Oct 03 09:03:50 g73jh postmaster[525267]: 2020-10-03 09:03:50.485 +07 [525267] LOG:  redirecting log output to logging >
Oct 03 09:03:50 g73jh postmaster[525267]: 2020-10-03 09:03:50.485 +07 [525267] HINT:  Future log output will appear in >
Oct 03 09:03:50 g73jh systemd[1]: Started PostgreSQL 12 database server.
lines 1-26/26 (END)

Change password for 'postgres' user

PS D:\xampp\PostgreSQL\16\bin> psql -U postgres
Password for user postgres:
psql (16rc1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.
postgres=# ALTER USER postgres PASSWORD 'lthwpg';
ALTER ROLE
postgres=# \q
PS D:\xampp\PostgreSQL\16\bin>

PostgreSQL on the web (without installation)

Postgres Playground

PostgreSQL WASM