====== PostgreSQL ======

[[https://www.postgresql.org|PostgreSQL]] is an open source relational database. It's a popular backend for many applications that need a database.

Also refer to [[https://wiki.debian.org/PostgreSql|Debian Wiki page for PostgreSQL]].


===== Installing =====

<code bash>
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
</code>
==== General configuration =====

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

<code bash>
vim /etc/postgresql/11/main/postgresql.conf
# Then restart it
systemctl restart postgresql
</code>

==== Configure authentication ====

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.

<code bash>
# 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
</code>

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.

<code bash>
# Get an admin sql shell
sudo -u postgres psql
</code>



===== Common tasks =====


==== Get a psql shell ====

<code bash>
# 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
</code>

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


==== List users ====

<code>
\du
</code>

==== List databases ====

<code>
\l
</code>

==== Create database ====

<code sql>
CREATE DATABASE mydb;
</code>

==== Create user ====

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

<code sql>
CREATE USER myuser WITH ENCRYPTED PASSWORD '$ecret';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
</code>

==== Change database ====

<code>
\c mydb
</code>

==== List tables ====

<code>
\dt
</code>

==== Show table schema ====

<code>
\d mytable
</code>

==== Data types ====

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

==== Backup ====

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

<code>
# 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
</code>

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