# - bof - #



# -  - #
# - Install PostgreSQL on Arch - #


:a
# -  - #
# - Update & reboot - #
sudo pacman -Syu;
sudo reboot now;



:b
# -  - #
# - Install PostgreSQL - #
sudo pacman -S postgresql --noconfirm;
postgres --version



:c
# -  - #
# - To test - #
# - Set applicable entries in /etc/locale.gen - #
#echo "en_US.UTF-8 UTF-8" | sudo tee /etc/locale.gen
#sudo locale-gen



:d
# -  - #
# - Initialize PostgreSQL - #
sudo systemctl status postgresql;
sudo su - postgres;
initdb --locale en_US.UTF-8 -D /var/lib/postgres/data
pg_ctl -D /var/lib/postgres/data -l logfile start
exit
logout



:e
# -  - #
# - Start PostgreSQL - #
sudo systemctl start postgresql;
sudo systemctl enable postgresql;
sudo systemctl status postgresql;



:f
# -  - #
# - Login PostgreSQL - #
sudo su - postgres
psql -c "alter user postgres with password 'StrongPassword'"
psql



:g
# -  - #
# - Create Database - #

CREATE DATABASE mydb;
\l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)



:h
# -  - #
# - Create user & grant all privileges - #

CREATE USER postgreuser WITH ENCRYPTED PASSWORD 'MyPassword';
GRANT ALL PRIVILEGES ON DATABASE mydb to postgreuser;



:i
# -  - #
# - Connect to database - #
\c mydb
mydb=#



:j
# -  - #
# - Changing PostgreSQL Service Port - #

# - Check net status - #
sudo netstat -nltp;

# - Change port - #
sudo vim /var/lib/postgres/data/postgresql.conf
port = 5433                             # (change requires restart)

# - Restart - #
sudo systemctl restart postgresql;

# - Check net status - #
sudo netstat -nltp;



:k
# -  - #
# - Files configuration - #
sudo vim /var/lib/postgres/data/postgresql.conf;
listen_addresses = '192.168.15.65'

sudo vim /var/lib/postgres/data/pg_hba.conf;
host    all             all             192.168.15.0/24            trust
host    replication     all             192.168.15.0/24            trust

sudo systemctl restart postgresql;



:l
# -  - #
# - Connection - #
psql -U <dbuser> -h <serverip> -p 5432 <dbname>



:m
# -  - #
# - Change PostgreSQL directory - #

# - Stop - #
sudo systemctl stop postgresql.service;

# - Create directory - #
sudo mkdir -p /data/postgres;
sudo chown postgres /data/postgres;
sudo chmod 700 /data/postgres;

# - Move PostgreSQL - #
sudo mv /var/lib/postgres/data /data/postgres;
sudo ln -s /data/postgres /var/lib/postgres/data;
sudo systemctl start postgresql.service;
sudo systemctl status postgresql





# - eof - #