PostgreSQL Cheats

Raw

Initialization of PostgreSQL 9.4 on CentOS.md

Overview

The following cheats apply to PostgreSQL 9.4 on CentOS (and also RHEL and standard Amazon Linux on EC2 instances).

Install and init DB

sudo yum install postgresql94 postgresql94-docs postgresql94-server postgresql94-test postgresql94-contrib
sudo service postgresql94 initdb

Update configs

Enable all connections

The following allows unrestricted connections over the network. Make sure that you read documentation to understand what you really doing. And please, note, that THIS IS UNSAFE, use for dev purposes only.

sudo vim /var/lib/pgsql94/data/pg_hba.conf

then add

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             0.0.0.0/0               md5
host    all             all             ::1/0                   md5

then do

sudo vim /var/lib/pgsql94/data/postgresql.conf

and add

listen_address='*'
port = 5432

Then restart your postgresql process:

sudo service postgresql94 restart

Appendix A - Create demo users

sudo su - postgres
psql -U postgres

and then create users:

ALTER USER postgres WITH PASSWORD '$password1';

CREATE USER pwr_user SUPERUSER;
ALTER USER pwr_user WITH PASSWORD '$password2';

CREATE USER demo_user NOSUPERUSER;
ALTER USER demo_user WITH PASSWORD '$password3';

CREATE DATABASE test_db WITH OWNER demo_user;

Example (just demo user + demo DB):

CREATE USER demo_user NOSUPERUSER;
ALTER USER demo_user WITH PASSWORD 'foo';
CREATE DATABASE test_db WITH OWNER demo_user;

Appendix B - Testing your changes

The following will connect to localhost, it wouldn't work unless you enabled network connections similarly to what shown above:

psql -h 127.0.0.1 -U $YOUR_USERNAME $YOUR_DB

# e.g.:
psql -h 127.0.0.1 -U demo_user test_db

Sample books table:

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title VARCHAR(1024) NOT NULL,
  pages INTEGER
);

INSERT INTO books (id, title, pages) VALUES (1, 'Noon', 392);
INSERT INTO books (id, title, pages) VALUES (2, 'Far Rainbow', 504);
INSERT INTO books (id, title, pages) VALUES (3, 'Hermit and Sixfinger', 156);
INSERT INTO books (id, title, pages) VALUES (4, 'Throttle', 94);
INSERT INTO books (id, title, pages) VALUES (5, 'Gunpowder', 211);
INSERT INTO books (id, title, pages) VALUES (6, 'The Time Wanderers', 405);
INSERT INTO books (id, title, pages) VALUES (7, 'Swans', 65);
INSERT INTO books (id, title, pages) VALUES (8, 'Audition', 275);

Raw

Install Latest JDBC Driver.md

Install Maven dependency:

mvn dependency:get -DartifactId=postgresql -DgroupId=org.postgresql -Dversion=LATEST

Raw

Setting Replication of PostgreSQL 9.4.md

Overview

This applies to PostgreSQL 9.4 on CentOS, RHEL and standard Amazon Linux on EC2 instances.

Preparations

sudo yum install postgresql94 postgresql94-docs postgresql94-server postgresql94-test postgresql94-contrib

Master Settings

Install database skeleton: sudo service postgresql94 initdb.

sudo su - postgres

Then vim /var/lib/pgsql94/data/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             0.0.0.0/0               md5
host    all             all             ::1/0                   md5

host    replication     replication     0.0.0.0/0               md5

Then vim /var/lib/pgsql94/data/postgresql.conf:

listen_addresses = '*'
port = 5432

# To enable read-only queries on a standby server, wal_level must be set to
# "hot_standby". But you can choose "archive" if you never connect to the
# server in standby mode.
wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.
max_wal_senders = 5

# To prevent the primary server from removing the WAL segments required for
# the standby server before shipping them, set the minimum number of segments
# retained in the pg_xlog directory. At least wal_keep_segments should be
# larger than the number of segments generated between the beginning of
# online-backup and the startup of streaming replication. If you enable WAL
# archiving to an archive directory accessible from the standby, this may
# not be necessary.
wal_keep_segments = 32

# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
#archive_mode    = on
#archive_command = 'cp %p /path_to/archive/%f'

Start DB sudo service postgresql94 restart.

Create sample user - test_db and replication user, as a postgres user, do psql -U postgres and then:

CREATE ROLE replication WITH REPLICATION PASSWORD 'foo' LOGIN;

CREATE USER demo_user NOSUPERUSER;
ALTER USER demo_user WITH PASSWORD 'foo';

CREATE DATABASE test_db WITH OWNER demo_user;

Then logout from psql and postgres shell, connect as demo_user and insert sample data:

psql -h 127.0.0.1 -U demo_user test_db

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title VARCHAR(1024) NOT NULL,
  pages INTEGER
);

INSERT INTO books (id, title, pages) VALUES (1, 'Noon', 392);
INSERT INTO books (id, title, pages) VALUES (2, 'Far Rainbow', 504);
INSERT INTO books (id, title, pages) VALUES (3, 'Hermit and Sixfinger', 156);
INSERT INTO books (id, title, pages) VALUES (4, 'Throttle', 94);
INSERT INTO books (id, title, pages) VALUES (5, 'Gunpowder', 211);
INSERT INTO books (id, title, pages) VALUES (6, 'The Time Wanderers', 405);
INSERT INTO books (id, title, pages) VALUES (7, 'Swans', 65);
INSERT INTO books (id, title, pages) VALUES (8, 'Audition', 275);

Slave Settings

Install postgresql, DO NOT run initdb script (you'll replicate dir from master).

sudo su - postgres

Then do

pg_basebackup -h $MASTER_HOST_IP -D /var/lib/pgsql94/data -P -U replication --xlog-method=stream

You'll be prompted with password, enter replication user password.

Then vim /var/lib/pgsql94/data/postgresql.conf:

hot_standby = on

Then create recovery file vim /var/lib/pgsql94/data/recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=$MASTER_HOST_IP port=5432 user=replication password=$REPLICATION_USER_PASSWROD'

# Optional:
#trigger_file = '/path/to/file/that/triggers/replication'
#restore_command = 'cp /path/to/archive/%f "%p"'

Then start database sudo service postgresql94 start.

Then, while still on slave host, login as demo_user and query books table psql -h 127.0.0.1 -U demo_user test_db:

SELECT * FROM books;

Make sure that inserts are not working on replica:

test_db=> INSERT INTO books (id, title, pages) VALUES (11, 'Dias', 392);
ERROR:  cannot execute INSERT in a read-only transaction

avshabanov/Initialization of PostgreSQL 9.4 on CentOS.md

Overview

Install and init DB

Update configs

Enable all connections

Links

Appendix A - Create demo users

Appendix B - Testing your changes

Overview

Preparations

Master Settings

Slave Settings

Links