Install PostgreSQL on Ubuntu 22.04
Remember to Set Correct System Locale before install PostgreSQL. This will make life easier.
Install PostgreSQL
sudo apt -y install postgresql postgresql-contrib
- config file: sudo vim /etc/postgresql/14/main/postgresql.conf
- config file: sudo vim /etc/postgresql/14/main/pg_hba.conf
Restart PostgreSQL
sudo /etc/init.d/postgresql restart
sudo /etc/init.d/postgresql status
Create user and database
Create user and database by SQL statements
sudo -u postgres psql postgres -c "CREATE USER {{ username.value }} WITH PASSWORD '{{ password.value }}';"
sudo -u postgres psql postgres -c "CREATE DATABASE {{ database.value }} ENCODING 'UTF8' OWNER {{ username.value }};"
Create user and database using command line tools (interactive)
sudo -u postgres createuser {{ username.value }}
sudo -u postgres createdb --encoding='UTF8' --owner={{ username.value }} {{ database.value }}
Manage PostgreSQL database
PostgreSQL management console: psql
sudo -u postgres psql postgres
sudo -u postgres psql {{ database.value }}
# Show all databases
\l+
# Connect to database
\c {{ database.value }}
# SHOW TABLES
\d+
# DESCRIBE TABLE
\d+ table
# Quit
\q
Backup PostgreSQL database {{ database.value }}
sudo -u postgres pg_dump --format=custom -U postgres {{ database.value }} > {{ filename }}
Restore PostgreSQL database {{ database.value }}
sudo -u postgres pg_restore -d {{ database.value }} {{ filename }}
sudo -u postgres pg_restore --no-owner --role={{ username.value }} -d {{ database.value }} {{ filename }}
Run SQL statement on the database {{ database.value }}
sudo -u postgres psql {{ database.value }}
sudo -u postgres psql {{ database.value }} -c '{{ sql.value }}'
How to Connect PostgreSQL from another machine (optional)
1) Listen to *
sudo {{ texteditor.value }} /etc/postgresql/14/main/postgresql.conf
listen_addresses = '*'
2) Allow remote user access
sudo {{ texteditor.value }} /etc/postgresql/14/main/pg_hba.conf
host all all 0.0.0.0/0 md5
3) Restart PostgreSQL
sudo /etc/init.d/postgresql restart
Set postgresql admin password (optional)
The admin role is postgres, we normally manage the database locally, thus that do not need any password. However, if connecting from another server to manage the database, the password may be required.
sudo -u postgres psql postgres
\password postgres
\q