PostgreSQL¶

Tips and Docs¶

Installation¶

Debian/Ubuntu

sudo apt update
sudo apt install postgresql postgresql-client postgresql-contrib

Start the Postgres service

sudo service postgresql start

Connect to PostgreSQL via pgsql (localhost)¶

Create a PostgreSQL user and database

sudo -u postgres psql

CREATE USER your_username WITH PASSWORD 'your_password';
CREATE DATABASE database_name;
GRANT ALL PRIVILEGES ON DATABASE database_name TO your_username;
\q

Check your database username and connection

psql --username pg_user --host pg_hostname --port pg_port --dbname pg_dbname

PG commands¶

\l
get the list of databases

\d
get the list of tables

\connect DBNAME
connect to the existing DB

Change PG password¶

\password postgres
OR
ALTER USER user_name PASSWORD 'new_password';

Print POSTGRES environment variables¶

env | grep -i -E 'POSTGRES|PG'

Connect from the other host¶

Find the line #listen_addresses = 'localhost' and change it to: listen_addresses = '*'
```
sudo vim /etc/postgresql/[version]/main/postgresql.conf
```

Change the interface listener for all connections

sudo vim /etc/postgresql/[version]/main/pg_hba.conf

host      all     all      0.0.0.0/0          md5