PostgreSQL

PostgreSQL is an open source relational database. It's a popular backend for many applications that need a database.

Also refer to Debian Wiki page for PostgreSQL.

apt-cache search postgresql
apt install postgresql-11
 
# Confirm it's running
systemctl status postgresql
# Confirm it's listening on tcp 5432
netstat -ntlp  # apt install net-tools
# By default, it will only listen locally

Edit the postgresql.conf file to change basic settings like port, listen address, and number of connections.

vim /etc/postgresql/11/main/postgresql.conf
# Then restart it
systemctl restart postgresql

By default, it will allow authentication by local Linux user account (peer) and also using regular username/password (md5) from localhost. To modify this, edit the pg_hba.conf file. In this file, change the IP ranges or methods for authentication to suit your needs.

# Edit the auth config
vim /etc/postgresql/11/main/pg_hba.conf
# In Fedora: /var/lib/pgsql/data/pg_hba.conf
# Then restart
systemctl restart postgresql

The installer will create a local user named postgres that you can use as the admin. Once you are logged in as an admin you can create databases and users as needed.

# Get an admin sql shell
sudo -u postgres psql

# Get admin shell
sudo -u postgres psql
# Connect to specific database
psql -d cathyplus
# Specify username, database, and host. (-W means ask for password)
psql -d cathyplus -U cathy -W -h localhost

Note, in MacOS, the shell is at /Library/PostgreSQL/12/scripts/runpsql.sh.

\du

\l

CREATE DATABASE mydb;

This will create a user with a password (for md5 auth) and grant them all privileges on a database (admin rights).

CREATE USER myuser WITH ENCRYPTED PASSWORD '$ecret';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

\c mydb

\dt

\d mytable

These are some of the data types that PostgreSQL supports. Referenced from https://www.postgresql.org/docs/9.5/datatype.html.

Common types:

• Integer (int/int4, int8)
• Float (float4, float8)
• Bit sequence (bit)
• Boolean (boolean)
• Text (varchar, char)
• Datetime (timestamp, date, time)
• Networking (inet, macaddr, cidr)
• Data (json, xml)
• money
• tsquery (text search query)
• tsvector (text search document)
• uuid

You can use the pg_dump tool to dump a database for backup.

# Using TCP and md5 auth
pg_dump -d mydb -h localhost -p 5432 -U myuser

# Using postgres admin with peer auth
sudo -u postgres pg_dump -d mydb

# Or
pg_dump -U postgres -d mydb

To restore, use the psql shell and pipe in the file.