postgresql

Install PostgreSQL and the phpPgAdmin web GUI on Ubuntu for browser-based database management.

Step 1 - Install PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib

Step 2 - Start and Enable

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 3 - Set a Password for the postgres User

sudo -u postgres psql

ALTER USER postgres WITH PASSWORD 'StrongPassword!';
\q

Step 4 - Install phpPgAdmin

sudo apt install phppgadmin

Step 5 - Configure Apache for phpPgAdmin

Edit /etc/apache2/conf-available/phppgadmin.conf and add Require all granted or restrict to your IP. Then:

sudo a2enconf phppgadmin
sudo systemctl reload apache2

Verify

Visit http://your-server-ip/phppgadmin and log in with the postgres credentials.

Install PostgreSQL on Ubuntu and perform initial configuration including creating a database user and enabling remote access.

Step 1 - Install

sudo apt update
sudo apt install postgresql postgresql-contrib

Step 2 - Start Service

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 3 - Initial Setup

sudo -u postgres psql

-- Set a password
ALTER USER postgres WITH PASSWORD 'StrongPassword!';

-- Create a new database
CREATE DATABASE myapp;

-- Create a new user
CREATE USER myuser WITH PASSWORD 'userpassword';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

\q

Step 4 - Allow Remote Connections (Optional)

Edit /etc/postgresql/14/main/postgresql.conf:

listen_addresses = '*'

Edit /etc/postgresql/14/main/pg_hba.conf and add:

host    all    all    YOUR_IP/32    md5

sudo systemctl restart postgresql

Verify

psql -U myuser -h localhost -d myapp

Configure pg_hba.conf to use trust authentication for localhost connections, allowing passwordless access during local development (e.g. for CartoDB or similar platforms).

Warning

Trust authentication disables password checks. Only use this on development machines, never in production.

Edit pg_hba.conf

Open /etc/postgresql/14/main/pg_hba.conf and update the local entries:

# TYPE   DATABASE  USER      ADDRESS       METHOD
local    all       postgres                trust
local    all       all                     trust
host     all       all       127.0.0.1/32  trust
host     all       all       ::1/128       trust

Apply Changes

sudo systemctl reload postgresql

Revert for Production

Replace trust with md5 or scram-sha-256 when deploying to production.

Common psql commands for connecting to PostgreSQL, managing users, and querying databases.

Connect as postgres User

sudo -u postgres psql

psql Meta-Commands

\l              -- list all databases
\c dbname       -- connect to a database
\dt             -- list tables in current database
\d table_name   -- describe a table
\du             -- list users/roles
\q              -- quit psql

User Management

-- Change postgres password
ALTER USER postgres WITH PASSWORD 'newpassword';

-- Create a new user
CREATE USER user1 WITH PASSWORD 'password123';

-- Grant superuser
ALTER USER user1 WITH SUPERUSER;

-- Grant database access
GRANT ALL PRIVILEGES ON DATABASE mydb TO user1;

Database Operations

-- Create database
CREATE DATABASE mydb;

-- Drop database
DROP DATABASE mydb;

-- Show running queries
SELECT pid, query, state FROM pg_stat_activity;

Use pg_dump and pg_restore to back up and restore PostgreSQL databases.

Dump a Database

# Plain SQL format
pg_dump -U postgres dbname > dbname.sql

# Custom compressed format (recommended for large DBs)
pg_dump -U postgres -Fc dbname > dbname.dump

# Dump all databases
pg_dumpall -U postgres > all_databases.sql

Restore a Database

# Restore from plain SQL
psql -U postgres dbname < dbname.sql

# Restore from custom format
pg_restore -U postgres -d dbname dbname.dump

# Restore with table of contents (selective)
pg_restore -U postgres -l dbname.dump > toc.txt
# edit toc.txt to select specific objects
pg_restore -U postgres -L toc.txt -d dbname dbname.dump

Become the postgres User First

sudo -i -u postgres
pg_dump dbname > dbname.sql

Notes

• Always use the custom format (-Fc) for databases larger than a few GB — it is compressed and supports parallel restore.
• The PostgreSQL version of the restore server should be equal to or newer than the source.