PostgreSQL

Notes are for Manjaro Linux unless states otherwise.

Initializing Database

If using ZFS create new ZFS file system, configure its parameters for the database and mount it to ‘/var/lib/postgres/data’. See ZFS page for detiled instructions.

On Manjaro PostgreSQL needs to be initialized. Command as root:

sudo su - postgres -c "initdb --locale en_US.UTF-8 -D '/var/lib/postgres/data'"

Start Database

systemctl start postgresql

References

Date, Time and TimeZone

1. Date and Time PostgreSQL documentation
2. DATE and TIME Functions
3. PostgreSQL Data Types: Date, Timestamp, and Time Zones
4. How to Query Date and Time in PostgreSQL

Other

1. GO Lang Driver

CLI Notes for PostgreSQL

psql Use To get to administrator prompt: sudo su - su - postgres psql DB Access Give user access to the database from IP address by editing file: sudo -u postgres vi /etc/postgresql/9.4/main/pg_hba.conf Add line: hostssl fireflyd firefly 10.0.1.13/31 md5 The line above allows firefly user to connect to fireflyd database from 10.0.1.13 IP address. Subnet /31 points to only one IP address. Reload configuration with:

IP Address

Use INET for IP address of the endpoint. Use CIDR for network address. Details

SQL Notes for PostgreSQL

High Availability and Balance Loading References PostgreSQL Documentation: Chapter 26. High Availability, Load Balancing, and Replication repmgr PostgreSQL Cluster does not match standard definition of cluster: What’s a PostgreSQL “Cluster” and how do I create one?. Wiki on replication. Create DB User and Database for Application Create DB User: CREATE USER name_u WITH LOGIN NOSUPERUSER CREATEROLE -- CREATEDB INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'type_password_here'; Create Database:

UUID in PostgreSQL

Generated Primary and Foreign Keys for Distributed Databases ID Conditions: unique to identify table row across databases. ordered for efficient clustered index performance. Examples UUID is not effective when data is spread across multi-master replication (MMR) scenario. It fails condition #2 above. -- to enable gen_random_uuid() function CREATE EXTENSION pgcrypto; CREATE SCHEMA IF NOT EXISTS core; create table core.user ( userPK UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(), created timestamp without time zone NOT NULL, updated timestamp without time zone NOT NULL ); References UUID Primary Keys in PostgreSQL