Database

PostgreSQL

What is PostgreSQL?

PostgreSQL is an open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. PostgreSQL is ACID-compliant and transactional, enabling developers to build applications with high integrity and reliability.

PostgreSQL

Installation

Ubuntu Server

Install from Repository

> sudo apt update
> sudo apt-get install postgresql postgresql-contrib \
libpq-dev
> sudo systemctl status postgresql

Install Latest Version

> curl -L https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor \
| sudo tee /usr/share/keyrings/postgresql-archive-keyring.gpg >/dev/null
> echo "deb [arch=amd64 signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee  /etc/apt/sources.list.d/postgresql.list
> sudo apt update
> sudo apt install postgresql libpq-dev
> sudo systemctl start postgresql

Test Installation

> sudo su - postgres
> psql
> postgres=# \conninfo

#return: You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Uninstall

Ubuntu Server

> sudo systemctl stop postgresql
> sudo apt remove --purge postgresql postgresql-*

Remove directories if necessary:

sudo rm -rf /etc/postgresql
sudo rm -rf /var/lib/postgresql
sudo rm -rf /var/log/postgresql

Delete related user:

> sudo deluser postgres

Clean up:

> sudo apt autoremove && sudo apt autoclean

Compile from Source

> sudo apt install build-essential
> sudo apt install libreadline-dev libncurses5-dev \
libssl-dev libxml2-dev libxslt-dev zlib1g-dev

Download source from PostgreSQL GitHub.

> tar -xvzf postgresql-X.Y.Z.tar.gz
> cd postgresql-X.Y.Z
> ./configure --prefix=/usr/local/pgsql
> make
> sudo make install

Initialize the database

> sudo mkdir -p /data/postgres
> sudo chown postgres:postgres /data/postgres
> sudo /usr/local/pgsql/bin/initdb -D /data/postgres

Security

Change Password

After first installation, change the default password for the postgres user.

> sudo passwd postgres

Next, change the password for the postgres user in PostgreSQL.

> sudo su - postgres
> psql -c "ALTER USER postgres WITH PASSWORD 'YOUR_NEW_PASSWORD';"
#return: ALTER ROLE
> exit

Configuration

Remote Access

To configure a PostgreSQL server to allow remote access, follow these steps:

  1. Locate the PostgreSQL configuration file (postgresql.conf). The file is usually located in the PostgreSQL data directory (e.g., /var/lib/pgsql/data or /etc/postgresql/<version>/main/).
> sudo nano /path/to/postgresql.conf
  1. Look for the line starting with listen_addresses and modify it to allow connections from the target IP address or all IPs:

listen_addresses parameter in PostgreSQL does not represent the PostgreSQL server’s IP address. Instead, it specifies the IP addresses or hostnames that the PostgreSQL server will “listen” on for incoming connections.

listen_addresses = '*'

If you want to allow only specific addresses, use:

listen_addresses = 'localhost,192.168.1.100'

  1. Save the file and close the editor.

  2. Open the pg_hba.conf file, which manages client authentication

Add a rule to allow connections from the remote IP address (replace 192.168.1.100 with your target IP or subnet):

host    all    all    192.168.1.100/32    md5

> sudo systemctl restart postgresql

Data Directory

  1. Stop PostgreSQL service:
> sudo systemctl stop postgresql
  1. Ensure PostgreSQL is completely stopped:
> sudo systemctl status postgresql
  1. Move the Data Directory
> sudo mkdir -p /data/new_pg_data
> sudo rsync -av /var/lib/postgresql/14/main/ /data/new_pg_data

Change ownership:

> sudo chown -R postgres:postgres /data/new_pg_data
> sudo chmod 700 /data/new_pg_data
  1. Edit the PostgreSQL configuration file:
> sudo nano /etc/postgresql/14/main/postgresql.conf

User Management

Get List of Users

> sudo -u postgres psql
> \du

Create a User

> sudo -u postgres createuser USER_NAME --no-createdb \
--no-superuser --no-createrole --pwprompt
> sudo -u postgres psql -c \
"alter user USER_NAME with superuser" postgres

Delete a User

> sudo -u postgres dropuser USER_NAME

Database Management

Get List of Database

> sudo -u postgres psql
> \l

Create Database

> sudo -u postgres createdb DB_NAME --owner=USER_NAME

Alternatively, you can create a database with the psql shell:

> sudo -u postgres psql
postgres=# CREATE DATABASE DB_NAME OWNER USER_NAME;

Delete Database

> sudo -u postgres psql
postgres=# DROP DATABASE database_name;

Create a schema

> sudo -u postgres psql
postgres=# CREATE SCHEMA schema_name;

Delete a schema

> sudo -u postgres psql
postgres=# DROP SCHEMA schema_name;

Table

Table Properties

  • Name
  • Comment
  • Persistence
    • PERSISTENT
    • UNLOGGED
    • TEMPORARY
  • Partition Expression
  • Partition Key
  • Options
  • Access Method: Defines how data is organized and accessed within a table or index in PostgreSQL
    • brin (Block Range Indexes): Efficient for very large tables with naturally ordered data, storing summaries of data ranges rather than individual entries.
    • btree (Balanced Tree): The default and most commonly used index method, best for range queries and equality lookups.
    • gin (Generalized Inverted Index): Optimized for indexing composite types, arrays, JSONB, and full-text search, allowing fast lookups of multiple values.
    • gist (Generalized Search Tree): Supports complex data types like geometric shapes, full-text search, and network addresses, enabling flexible indexing strategies.
    • hash: Used for simple equality searches but less commonly recommended due to historical limitations (improved in recent versions).
    • heap: The default storage method for tables, where data is stored in unordered pages without an inherent access method.
    • spgist (Space-Partitioned Generalized Search Tree): Suitable for dynamic data structures such as points, geometric data, and text search with efficient partitioning.
Previous
MongoDB
Next
Redis