Set up PostgresSQL database

1. Download from https://www.enterprisedb.com/download-postgresql-binaries

2. Install PostgreSQL database by unzip the binary package to a local folder, c:\pgsql. Alternatively, a GUI-installation can be downloaded for easier installation.

3. Set up environment in Windows System Environment or a batch file. NOTE: you may need to create a subfolder called \data
     c:\> SET PGDATA=c:\pgsql\data
     c:\> SET PATH=%PATH%;c:\pgsql\bin

4. Initialize DB (Note: without SET PGDATA, need to add -D c:\pgsql\data when calling initdb). For authentication method, you can choose scram-sha-256, md5 or password(in plain text, not recommended due to the "sniff" attack)
     c:\pgsql> initdb --auth-local=scram-sha-256

5. Start up DB service (Note: -l is to specify the log file).
     c:\pgsql> pg_ctl -l logfile start
     (in Ubuntu) systemctl start postgresql
6. Create a database
     c:\pgsql> createdb mydb

7. Create a DB user (-s option is for super user)
    c:\pgsql> createuser -s postgres

8. Connect to the database from Postgres client console, mydb
     c:\pgsql> psql -h localhost -d mydb

9. List instances from a database, mydb (Note: use parameter \l or \list)
     mydb# \list

10. Connect/switch to another database, postgres (Note: use parameter \c or \connect)
     mydb# \connect postgres

11. List all tables from the current database
     mydb# SELECT * FROM Information_Schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');

12. List all fields of a table
    mydb# \d test_user_tab;
    or
    mydb# SELECT * FROM Information_Schema.columns WHERE table_name = 'test_user_tab';

13. Quit Postgres client console (Note: use parameter \q or \quit)
     mydb# \quit

14. Change password in Postgres client console
     mydb# \password new_password

15. Check DB server Status
     c:\pgsql> pg_ctl status
     (in Ubuntu) systemctl status postgresql
16. Shut down DB Server
     c:\pgsql> pg_ctl stop
     (in Ubuntu) systemctl stop postgresql
17. Show help information (use --help in the MS-DOS command; or \? in Postgres client console)
     c:\pgsql> psql --help

18. Common SQL statements
     CREATE DATABASE my_db; -- create a database
     CREATE SCHEMA demo_sch; --create a schema
     CREATE USER xuser PASSWORD 'xuser123'; --create a database role/user
     GRANT ALL ON SCHEMA demo_sch TO xuser; --grant all permissions of a schema to a user
     GRANT ALL ON ALL TABLES IN SCHEMA demo_sch TO xuser; --grant all permission of all tables in a schema to a user
     ALTER USER xuser WITH PASSWORD 'new_password'; --change user password

     CREATE TABLE test_user_tab (name VARCHAR(10), age INT, registered_at TIMESTAMP, balance DECIMAL(10,2)); --create a table named test_user_tab
     
     INSERT INTO test_user_tab (name, age, registered_at, balance) VALUES ('jon', 30, TO_TIMESTAMP('1988-01-01 13:55:55','YYYY-MM-DD HH24:MI:SS'), 1234.56); --insert a record into the table
     SELECT * FROM test_user_tab; --query the table
     UPDATE test_user_tab SET name = 'jonathan' WHERE name = 'jon';
     SELECT name, age, TO_CHAR(registered_at, 'YYYY-MM-DD HH24:MI'), balance FROM test_user_tab; --query the table by specific columns
     DELETE FROM test_user_tab WHERE name = 'jonathan';

     DROP TABLE test_user_tab;

19. Reset database user password (version 12)

• open /etc/postgresql/12/main/pg_hba.conf and update the following line by replacing md5 to trust, so no password required

 local   all             postgres                                md5

• restart postgresql database
• login postgresql using the DB user id without needing a password. e.g. postgres
• run the following SQL to change/reset the password
• ALTER USER postgres WITH PASSWORD 'newpassword';
• open /etc/postgresql/12/main/pg_hba.conf and revert the previous change from trust back to md5
• restart the database again