PostgreSQL

1. Information

2. Linux

2.1. Installation

sudo apt-get update
sudo apt-get install postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql
sudo systemctl start  postgresql
sudo systemctl status postgresql
psql --version                          # psql (PostgreSQL) 17.7 (Debian 17.7-0+deb13u1)

2.2. Configuration

Enter the following commands at a Command Line.

sudo su - postgres -c 'psql --list'

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)

Enter the following commands at a Command Line.

sudo su - postgres -c 'psql --dbname postgres --command "\du+"'
sudo su - postgres -c 'createuser --createdb --inherit --login --no-createrole --pwprompt --superuser smru'
sudo su - postgres -c 'createuser --no-superuser --no-createdb --no-createrole --login --no-inherit --pwprompt anc'
sudo su - postgres -c 'psql --dbname postgres --command "\du+"'
psql --dbname postgres --command '\du+'

2.3. Debug

Enter the following commands at a Command Line.

sudo tail -f /var/log/postgresql/postgresql-*-main.log

2.4. ANC Application

Append the following lines to the /etc/postgresql/13/main/pg_hba.conf file.

# Local network connections.
host        all             all             10.10.1.0/24    md5     # SMRU LAN
host        all             all             192.168.1.0/24  md5     # Wifi

Append the following lines to the /etc/postgresql/13/main/postgresql.conf file.

# Local network connections.
listen_addresses = '*'

sudo pg_ctlcluster 13 main restart

psql --host localhost     --username smru --list
psql --host 10.10.1.155   --username smru --list        # SMRU LAN
psql --host 192.168.1.103 --username smru --list        # Wifi

psql -d anc-wal -c "SELECT * FROM \"tblPregnancy\" WHERE \"PID\" = '104999';"

2.5. Population

tbhf-anc-mrm:   sudo -u postgres pg_dump --column-inserts --file /tmp/anc-active.bak anc-active
                sudo -u postgres pg_dump --column-inserts --file /tmp/anc-entire.bak anc-entire
                cp -a /tmp/anc-active.bak /home/Other/Tmp
                cp -a /tmp/anc-entire.bak /home/Other/Tmp

DB="anc-active"
dropdb $DB
createdb --encoding UTF8 --owner anc $DB
FILE=/mnt/c/Tmp/anc-active.bak
psql --dbname $DB --file $FILE --quiet --single-transaction
psql --dbname $DB --file $FILE --single-transaction

2.6. Logical Replication

2.6.1. Publisher side

Turn off Windows Defender Firewall on the Windows host when using WSL.
Add the following line to the /etc/postgresql/13/main/pg_hba.conf file.
```
host    all     repuser 10.10.1.0/24    md5
```
Add the following line to the /etc/postgresql/13/main/postgresql.conf file.
```
wal_level = logical
```

psql --list
createdb --encoding UTF8 --owner anc dah
psql --list

psql --dbname dah --command "\dt+;"
psql --dbname dah --command "CREATE TABLE table1(x INT PRIMARY KEY, y INT);"
psql --dbname dah --command "\dt+;"

psql --dbname dah --command "SELECT * FROM table1;"
psql --dbname dah --command "INSERT INTO table1 VALUES(10, 11);"
psql --dbname dah --command "SELECT * FROM table1;"

psql --dbname postgres --command '\du+'
psql --dbname postgres --command "CREATE ROLE repuser REPLICATION LOGIN PASSWORD 'password';"
sudo pg_ctlcluster 13 main restart
psql --dbname postgres --command '\du+'

psql --dbname dah --command '\dRp+'
psql --dbname dah --command "CREATE PUBLICATION dah_pub FOR TABLE table1;"
psql --dbname dah --command '\dRp+'

psql --dbname dah --command '\dp';
psql --dbname dah --command "GRANT SELECT ON table1 TO repuser;"
psql --dbname dah --command '\dp';

2.6.2. Subscriber side

psql --list
createdb --encoding UTF8 --owner anc dah
psql --list

psql --dbname dah --command "\dt+;"
psql --dbname dah --command "CREATE TABLE table1(x INT PRIMARY KEY, y INT);"
psql --dbname dah --command "\dt+;"

psql --dbname dah --command "SELECT * FROM table1;"

psql --dbname dah --command "\dRs+"
psql --dbname dah --command " \
  CREATE SUBSCRIPTION dah_sub \
  CONNECTION 'host=10.10.1.77 port=5432 dbname=dah user=repuser password=password' \
  PUBLICATION dah_pub;"
psql --dbname dah --command "\dRs+"

psql --dbname dah --command "SELECT * FROM table1;"

2.7. Synchronisation

See https://www.postgresql.org/docs/9.3/postgres-fdw.html.

psql postgresql://10.10.1.77:5432/anc-active --command '\du+'

3. Windows

See http://www.postgresql.org/download/windows

3.1. PostgreSQL 8.4

Note: Do not install PostgreSQL v8.4.9, because its pg_restore fails to restore backups from PostgreSQL v8.3.14.

http://get.enterprisedb.com/postgresql/postgresql-8.3.7-1-windows.exe
http://get.enterprisedb.com/postgresql/postgresql-8.3.14-1-windows.exe
http://get.enterprisedb.com/postgresql/postgresql-8.3.15-1-windows.exe

3.1.1. Installation

Log on as Administrator.
Double-click the postgresql-8.4.4-1-windows.exe file.
Click Next.

C:\Program Files\PostgreSQL\8.4

Click Next.

C:\Program Files\PostgreSQL\8.4\data

Click Next.

Password:               <level 2 password>
Retype password:        <level 2 password>

Click Next.

Port:                   5432

Click Next.

Locale:                 [Default locale]

Check Install pl/pgsql in template1 database?.
Click Next.
Click Next.
Uncheck Stack Builder may be used to download and install additional tools, drivers and applications to complement your PostgreSQL installation.
Click Finish.

3.1.2. Configuration

Log on as Administrator.
Select Start > Programs > PostgreSQL 8.4 > pgAdmin III.
Select File > Add Server.

Name                    Mae Sot
Host                    10.10.1.2
Port                    5432
SSL                     disable
Maintenance DB          postgres
Username                smru
Password                <level 2 password>
Store password          □
Restore env?            ■
DB restriction
Service
Connect Now             □
Colour

Click OK.

Right-click Mae Sot (10.10.1.2:5432) and select Connect.
Type the level 2 password.
Uncheck Store password.
Click OK.
Check Do not show this hint again.
Click OK.
Right-click Mae Sot (10.10.1.2:5432) and select Disconnect.
Select File > Exit.

3.1.3. Usage

3.1.3.1. Restore of WPA database

Restore of wangpha database on "Mae Sot (10.10.1.2:5432)" server:

Log on as <User>.
Select Start > Programs > PostgreSQL 8.4 > pgAdmin III.
Right-click PostgreSQL 8.4 (localhost:5432) and select Connect.
Type the level 2 password and select OK.
Right-click Login Roles and select New Login Role.
Select the Properties tab.

Role name:              smru
Password:               <level 2 password>
Password (again):       <level 2 password>

Select the Role Privileges tab.

■ Inherits rights from parent roles
□ Superuser
■ Can create database objects
■ Can create roles
□ Can modify catalog directly

Click OK.
Right-click Databases and select New Database.
Select the Properties tab.

Name:                   smrudb
Owner:                  smru
Encoding:               UTF8
Template:
Tablespace:             <default tablespace>
Connection Limit:       -1

Click OK.
Right-click Databases > smrudb and select Restore.

Filename:       T:\IT\Projects\TSFI\Windows\2010-07-02-wangpha-smrudb.backup

□ Only data
□ Only schema
□ Single object

■ No owner
■ Disable Trigger
□ Clean before restore

□ Verbose messages

Click OK.
Select File > Exit.

3.1.3.2. Restore of WPA database

Restore of wangpha database on "PostgreSQL 8.4 (localhost:5432)" server

Log on as <User>.
Select Start > Programs > PostgreSQL 8.4 > pgAdmin III.
Right-click PostgreSQL 8.4 (localhost:5432) and select Connect.
Type the level 2 password and select OK.
Right-click Login Roles and select New Login Role.
Select the Properties tab.

Role name:              smru
Password:               <level 2 password>
Password (again):       <level 2 password>

Select the Role Privileges tab.

■ Inherits rights from parent roles
□ Superuser
■ Can create database objects
■ Can create roles
□ Can modify catalog directly

Click OK.
Right-click Databases and select New Database.
Select the Properties tab.

Name:                   smrudb
Owner:                  smru
Encoding:               UTF8
Template:
Tablespace:             <default tablespace>
Connection Limit:       -1

Click OK.
Right-click Databases > smrudb and select Restore.

Filename:       T:\IT\Projects\TSFI\Windows\2010-07-02-wangpha-smrudb.backup

□ Only data
□ Only schema
□ Single object

■ No owner
■ Disable Trigger
□ Clean before restore

□ Verbose messages

Click OK.
Select File > Exit.

3.2. PostgreSQL 9.4

3.2.1. Installation

Run the postgresql-9.5.4-1-windows-x64.exe file with administrative privileges.
Click Next.

C:\Program Files\PostgreSQL\9.4

Click Next.

C:\Program Files\PostgreSQL\9.4\data

Click Next.
Provide the password for the database superuser (postgres).

Password:               <level 1 password>
Retype password:        <level 1 password>

Click Next.

Port:                   5432

Click Next.

Locale:                 [Default locale]

Click Next.
Click Next.
Wait.
Uncheck Stack Builder may be used to download and install additional tools, drivers and applications to complement your PostgreSQL installation.
Click Finish.
Append C:\Program Files\PostgreSQL\9.4\bin to the PATH environment variable.

3.2.2. Configuration

Log on as Administrator.
Select Start > Programs > PostgreSQL 9.4 > pgAdmin III.
Right-click Servers > PostgreSQL 9.4 (localhost:5432) and select Properties.
Select the Properties tab.

Name                                    PostgreSQL 9.4
Host                                    localhost
Port                                    5432
Service
Maintenance DB
Username                                smru
Password                                <level 2 password>
Store password                          □
Colour
Group                                   Servers

Select the SSL tab.

SSL                                     disable
Server Root Certificate File
Server Certificate Revocation List
Client Certificate File
Client Key File
SSL Compression                         ■

Select the SSH Tunnel tab.
Select the Advanced tab.

Host Address
Connect now                             □
Restore env?                            ■
Rolename
DB restriction
Service ID                              postgresql-x64-9.4

Click OK.
Right-click Mae Sot (10.10.1.2:5432) and select Connect.
Type the level 2 password.
Uncheck Store password.
Click OK.
Check Do not show this hint again.
Click OK.
Right-click Mae Sot (10.10.1.2:5432) and select Disconnect.
Select File > Exit.

3.2.3. Usage

psql --help
# psql --host localhost --username postgres --list
psql --username postgres --list
psql --username postgres
\?
\q
psql --command "\du+" --dbname postgres --username postgres
psql --username postgres
DROP ROLE smru;
DROP ROLE anc;
# Note: Replace the asteriks by the level 2 password in the following commands.
CREATE ROLE smru PASSWORD '********' CREATEDB INHERIT LOGIN NOCREATEROLE SUPERUSER;
CREATE ROLE anc PASSWORD '********' NOCREATEDB NOINHERIT LOGIN NOCREATEROLE NOSUPERUSER;
\q
psql --command "\du+" --dbname postgres --username postgres

psql --username postgres
\l
DROP DATABASE "anc-mla";
CREATE DATABASE "anc-mla" OWNER smru;
\l
\q

pg_restore --dbname anc-mla --single-transaction --username smru D:\Tmp\2015-11-11-tbhf-anc-mla-anc-mla.backup

psql --dbname anc-mla --username smru
SELECT * FROM "Users";
\q

pg_restore --dbname anc-wpa --single-transaction --username smru D:\Tmp\2015-12-08-tbhf-anc-wpa-anc-wpa.backup
pg_restore --dbname anc-wpa --single-transaction --username smru D:\Tmp\2015-12-09-tbhf-anc-wpa-anc-wpa.backup
pg_restore --dbname anc-wpa --single-transaction --username smru D:\Tmp\2015-12-10-tbhf-anc-wpa-anc-wpa.backup
pg_restore --dbname anc-wpa --single-transaction --username smru D:\Tmp\2015-12-15-tbhf-anc-wpa-anc-wpa.backup

psql --dbname anc-wpa --username smru
SHOW search_path;
SET search_path TO "$user",public,active;
SELECT * FROM "tblEnrolment" WHERE "ANC_code" = 'MLA-24000';
SELECT * FROM "tblEnrolment" WHERE "ANC_code" = 'WPA-12140';
SELECT * FROM "tblWeeklyExam" WHERE "ANC_code" = 'WPA-12140';

3.3. PostgreSQL 13.2

3.3.1. Installation

Run the postgresql-13.2-1-windows-x64.exe file with administrative privileges.

Click Next.

Installation Directory  C:\Program Files\PostgreSQL\13

Click Next.

+ PostgreSQL Server
+ pgAdmin 4
+ Stack Builder
+ Command Line Tools

Click Next.

Data Directory          C:\Program Files\PostgreSQL\13\data

Click Next.

Provide the password for the database superuser (postgres).

Password:               <level 1 password>
Retype password:        <level 1 password>

Click Next.
```
Port:                   5432
```

Click Next.

Locale:                 [Default locale]

Click Next.

Installation Directory: C:\Program Files\PostgreSQL\13
Server Installation Directory: C:\Program Files\PostgreSQL\13
Data Directory: C:\Program Files\PostgreSQL\13\data
Database Port: 5432
Database Superuser: postgres
Operating System Account: NT AUTHORITY\NetworkService
Database Service: postgresql-x64-13
Command Line Tools Installation Directory: C:\Program Files\PostgreSQL\13
pgAdmin4 Installation Directory: C:\Program Files\PostgreSQL\13\pgAdmin 4
Stack Builder Installation Directory: C:\Program Files\PostgreSQL\13

Click Next.
Click Next.
Wait several minutes for the installation to finish.
Uncheck Stack Builder may be used to download and install additional tools, drivers and applications to complement your PostgreSQL installation.
```
- Stack Builder may be used to download and install
  additional tools, drivers and applications to
  complement your PostgreSQL installation.
```
Click Finish.
Append C:\Program Files\PostgreSQL\13\bin to the PATH environment variable.

3.4. Usage

3.5. PostgreSQL ODBC driver

3.5.1. Installation

Note: This driver and configuration is needed by PMS clients that make birth certificates.
Note: The latest PostgreSQL ODBC drivers can be downloaded from http://www.postgresql.org/ftp/odbc/versions/msi.
Note: Make sure to install the 32-bit ODBC driver for 32-bit Microsoft Access.
Note: Make sure to install the 64-bit ODBC driver for 64-bit Microsoft Access.
Log on as Administrator.
32-bit Microsoft Office: Run the psqlodbc_13_02_0000-x86.msi file.
64-bit Microsoft Office: Run the psqlodbc_13_02_0000-x64.msi file.
Click Next.
Check I agree to the license terms and conditions.
Click Next.

psqlODBC
  ■ ODBC Driver
  □ Documentation

C:\Program Files (x86)\psqlODBC

Click Next.
Click Install.
Wait.
Click Finish.

3.5.2. ANC Data Report

Configuration for ANC Data Report (method 1):

Selecting Control Panel > Administrative Tools > Data Sources (ODBC) selects the 64-bit version.

Log on as Administrator.
Select Start > Run.
32-bit: Type %windir%\SysWOW64\odbcad32.exe for the 32-bit version and select OK.
64-bit: Type %windir%\System32\odbcad32.exe for the 64-bit version and select OK.
Select the System DSN tab.
Click Add.
32-bit: Select PostgreSQL Unicode.
64-bit: Select PostgreSQL Unicode(x64).
Click Finish.

Data Source     anc-active              Description     ANC Data Report
Database        anc-active              SSL Mode        disable
Server          10.10.1.2               Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     anc-mkt                 Description     ANC Data Report
Database        anc-mkt                 SSL Mode        disable
Server          10.10.1.2               Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     anc-mla                 Description     ANC Data Report
Database        anc-mla                 SSL Mode        disable
Server          10.10.1.2               Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     anc-mlc                 Description     ANC Data Report
Database        anc-mlc                 SSL Mode        disable
Server          10.10.1.2               Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     anc-ppl                 Description     ANC Data Report
Database        anc-ppl                 SSL Mode        disable
Server          10.10.1.2               Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     anc-wal                 Description     ANC Data Report
Database        anc-wal                 SSL Mode        disable
Server          10.10.1.2               Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     anc-wpa                 Description     ANC Data Report
Database        anc-wpa                 SSL Mode        disable
Server          10.10.1.2               Port            5432
User Name       smru                    Password        <level 2 password>

Click Datasource.
Click Page 1.
Uncheck Bools as Char.
Click Page 2.
Check True is -1.
Uncheck Updatable Cursors.
Click Apply.
Click OK.
Click Test.
Click OK.
Click Save.
Click OK.

3.5.3. Usage for ANC Data Report

Start Microsoft Office Access 2010.
Select the External Data tab.
Select ODBC Database.
Choose Import the source data into a new table in the current database.
Click OK.
Select the Machine Data Source tab.
Select PostgreSQL35W > System > ANC Data Report.
Click OK.
Optional: Select the active.* tables?
Optional: Click OK?
Optional: Uncheck Save import steps?
Optional: Click Close?
Close Microsoft Office Access 2010.

Configuration for ANC Data Report (method 2):

Open Blank Access database.
Click Folder icon to locate and name the file directory.
Click Create.
Click External Data.
Click ODBC Database.
Choose Import the source data into a new table in the current database.
Click OK.
Select File Data Source tab.
Click New.
Select PostgreSQL Unicode.
Click Next.
Click Browse.
Type <Name>. in File name: text box.
Click Save.
Click Next.
Click Finish.

Data Source     PostgreSQL35W           Description     ANC Data Report
Database        anc-mkt                 SSL Mode        disable
Server          tbhf-anc-mkt            Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     PostgreSQL35W           Description     ANC Data Report
Database        anc-mla                 SSL Mode        disable
Server          tbhf-anc-mla            Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     PostgreSQL35W           Description     ANC Data Report
Database        anc-wpa                 SSL Mode        disable
Server          tbhf-anc-wpa            Port            5432
User Name       smru                    Password        <level 2 password>

Data Source     PostgreSQL35W           Description     ANC Data Report
Database        anc-active              SSL Mode        disable
Server          tbhf-anc-mrm            Port            5432
User Name       smru                    Password        <level 2 password>

Click OK.
Select <Name>.dsn.
Click OK.
Type the level 2 password in the Password text box.
Select Tables that you need.
Click OK.
Click Close.

3.6. Configuration of Stack Builder

Log on as Administrator.
Select Start > Programs > PostgreSQL 8.4 > Application Stack Builder.
Select PostgreSQL 8.4 on port 5432.
Click Proxy servers.

HTTP Proxy:             10.10.1.170     Port:   8080
FTP Proxy:              10.10.1.170     Port:   8080

Click OK.
Click Next.

Failed to open the application list http://www.postgresql.org/applications-v2.xml
Error: The URL specified could not be opened.

Click OK.
Click OK.
Click Yes.

PostgreSQL

Home

1. Information

2. Linux

2.1. Installation

2.2. Configuration

2.3. Debug

2.4. ANC Application

2.5. Population

2.6. Logical Replication

2.6.1. Publisher side

2.6.2. Subscriber side

2.7. Synchronisation

3. Windows

3.1. PostgreSQL 8.4

3.1.1. Installation

3.1.2. Configuration

3.1.3. Usage

3.1.3.1. Restore of WPA database

3.1.3.2. Restore of WPA database

3.2. PostgreSQL 9.4

3.2.1. Installation

3.2.2. Configuration

3.2.3. Usage

3.3. PostgreSQL 13.2

3.3.1. Installation

3.4. Usage

3.5. PostgreSQL ODBC driver

3.5.1. Installation

3.5.2. ANC Data Report

3.5.3. Usage for ANC Data Report

3.6. Configuration of Stack Builder