52  PostgreSQL 101

52.1 Install

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

sudo apt-get install -y postgresql-12

sudo apt-mark hold postgresql-12

Verify that PostgreSQL is running:

sudo systemctl status postgresql

52.2 User Setup

Set a password for the postgres database user:

sudo -u postgres psql postgres

\password postgres

\q

52.3 PostgreSQL connection URI

The PostgreSQL connection URI follow the format

postgres[ql]://[username[:password]@][host[:port],]/database[?parameter_list]

52.4 Query database

psql -h localhost -U postgres postgres -c '\list;'

Did not find any relations.

52.5 Create new database

sudo -u postgres createdb \
    --owner=username \
    --encoding=UTF8 \
    --template=template0 \
    dbname

52.6 List databases

\list;

52.7 Switch to database

Note

The use of double quotes is recommended to avoid the expression being interpreted.

\c "database-name";

52.8 List tables in database

\dt;

52.9 Change table ownership

Note

The use of double quotes is recommended to avoid the expression being interpreted.

ALTER TABLE "table-name" OWNER TO "username";

52.10 Uninstall

sudo apt -y --purge remove postgresql postgresql-*

sudo apt -y --purge autoremove

sudo deluser postgres

sudo rm -rf /etc/postgresql

sudo rm -rf /etc/postgresql-common/

sudo rm -rf /usr/local/pgsql

sudo rm -rf /var/log/postgresql/