Useful Postgresql commands/sqls for beginners

The PostgreSQL cluster is in recovery (or is a streaming replication standby). You can find out if that is the case by running
SELECT pg_is_in_recovery();
The parameter default_transaction_read_only is set to on. Diagnose with
SHOW default_transaction_read_only;
The current transaction has been started with. You can find out if that is the case using the undocumented parameter
SHOW transaction_read_only;
where is pgdata?
SHOW data_directory;
Replication user is active?
select * from pg_stat_activity where usename = 'replicator';

PSQL Listing Commands


\x on; --extended display
\c db; --connect to database
\du+; --list users
\dt+ schema.tablename; --lists tables
\df+ schema.func/procname;
\d+  schema.tablename; -- gives description of table

Minimal Postgresql 11 Replication on Ubuntu 18.04

I have two Virtual Machines/Servers with Ubuntu 18.04 installed on it. On both these machines I have Postgresql 11 installed. Let us say the IP of master is 127.10.10.1 and IP of slave is 127.10.10.2

Changes to Master

Firstly we shutdown master

sudo service postgresql stop

Go to psql as postgres and create a replication user called replicator.

CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'replica'; 

Go to pg_hba.conf file which is in /etc/postgresql/11/main folder

add replication user with slave ip

#add replication user with slave ip
host replication replicator 127.10.10.2/32 md5

Go to postgresql.conf file which is in /etc/postgresql/11/main folder

#change listen_address to allow all ips with  
# listen_addresses = '*'
# OR mention specifically the slave ip
listen_addresses = 'localhost, 127.10.10.2'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Start pg master

sudo service postgresql start

Slave Configuration

Stop the slave

sudo service postgresql stop

Go to pg_hba.conf file which is in /etc/postgresql/11/main folder

add replication user with master ip

#add replication user with master ip
host replication replicator 127.10.10.1/32 md5

Go to postgresql.conf file which is in /etc/postgresql/11/main folder

#change listen_address to allow all ips with  
# listen_addresses = '*'
# OR mention specifically the slave ip
listen_addresses = 'localhost, 127.10.10.2'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

One more very important part of the postgresql.conf to do is set the slave in standby mode. That way it is in “read only” and just replicates events that happen in the master.

standby_mode = 'on'

Now we need to do a “first” copy of data from the master to the slave. Which implies we need to delete all data in postgresql on the slave and take everything thats on the master. Now first let’s find out where the postgresql data (PGDATA) is .

-- login as user postgres
$ sudo -i -u postgres
--login to psql
$psql
--to find out where pgdata is:
$show data_directory;
data_directory
/var/lib/postgresql/11/main
(1 row)

Now we go to that folder and make a backup and remove main. Please note that one has to be logged in as user postgres in order to access main folder

$mv main main_bkup
$rm -rf main

Here comes the real deal now. We have to connect to the master and take the master’s pgdata and move it to slave. There’s one elegant command for that:

$ pg_basebackup -h master-ip -D /var/lib/postgresql/11/main/ -P -U  replicator --wal-method=stream
-- when this run, the following output is printed
Password:
waiting for checkpoint
0/46100 kB (0%), 0/1 tablespace
17829/46100 kB (38%), 0/1 tablespace
46109/46109 kB (100%), 0/1 tablespace
46109/46109 kB (100%), 1/1 tablespace

Now we go inside the main folder and create a recovery.conf file. This too has to be done as postgres user.

$ cd /var/lib/postgresql/11/main/
$ touch recovery.conf

The contents of recovery.conf are

standby_mode         = 'on'
primary_conninfo = 'host=master-ip port=5432 user=replicator password=my_replicator_password'
trigger_file = '/tmp/MasterNow'

Start pg slave

sudo service postgresql start

Testing Replication

On the master, see if the replication user shows activity

select * from pg_stat_activity where usename = 'replicator';
--gives the following output
-[ RECORD 1 ]----+------------------------------
datid |
datname |
pid | 27253
usesysid | 16597
usename | replicator
application_name | walreceiver
client_addr | 127.10.10.2
client_hostname |
client_port | 42290
backend_start | 2019-07-03 09:15:13.939534+02
xact_start |
query_start |
state_change | 2019-07-03 09:15:13.948509+02
wait_event_type | Activity
wait_event | WalSenderMain
state | active
backend_xid |
backend_xmin |
query |
backend_type | walsender

You can now create a table in master add rows to it

--in psql from master--
create table test (msg varchar(255), created_on date);
insert into test values ('test1' , current_date);

We can now select these values from the slave.

--in psql from slave --
select * from test;
-[ RECORD 1 ]-----
msg | test1
datum | 2019-07-03

When we try to insert in the slave then we get error

insert into test values ('hari', current_date);
ERROR: cannot execute INSERT in a read-only transaction

This is because the slave is in standby mode. It can only be replicated from master. You can check these values with following commands:

The PostgreSQL cluster is in recovery (or is a streaming replication standby). You can find out if that is the case by running
SELECT pg_is_in_recovery();
--The parameter default_transaction_read_only is set to on. Diagnose with
SHOW default_transaction_read_only;

Happy replicating!! At this point i would also like to thank the innumerable blogs of other great techies that put out this information out there.

Installing oracle_fdw in Postgresql-11 on Ubuntu18.04

To install the oracle foreign data wrapper on Postgres, firstly we need to make sure we are able to connect to Oracle from our Ubuntu machine.

For this, make sure that all the Oracle components are installed. I followed the beautiful step by step instructions given here:
https://mikesmithers.wordpress.com/2011/04/03/oracle-instant-client-on-ubuntu-with-added-aliens/

After that, test your oracle connection using this sqlplus format

sqlplus oraschemaname/oraschemapassword@//hostname:1521/Service

e.g sqlplus xx/yy@//abc.def.com:1521/SERVICE

If you have connected succesfully to Oracle then we can now get busy with the installation of oracle_fdw

I downloaded the fdw from here: https://github.com/laurenz/oracle_fdw/releases/

Naturally all credit for everything that I documented here goes to this person who created the oracle_fdw. I am only trying to document my experience of installing and what difficulties i faced.

After downloading the zip file oracle_fdw-ORACLE_FDW_2_1_0.zip, i unzipped it into a folder with the same name oracle_fdw-ORACLE_FDW_2_1_0

The instructions say that one goes into the folder in command prompt and then types make.

/home/ana/Downloads/oracle_fdw-ORACLE_FDW_2_1_0$ make

You need to install postgresql-server-dev-NN for building a server-side extension or libpq-dev for building a client-side application.
make: *** No targets. Stop.

So when you see this, you need to install some missing components of postgres. So we run the following installation:

sudo apt-get install postgresql-server-dev-11

Now rerun make, and it will get all the source files ready to be recompiled.

After the make, one has to install oracle_fdw with $make install

But in order for the installation to work, certain places where postgres is installed should get a write access. When they have a write access then, the installation of the fdw will be a success.

 ana@ana-pg % sudo chmod a+rwx /usr/share/doc
ana@ana-pg % sudo chmod a+rwx /usr/lib/
ana@ana-pg % sudo chmod a+rwx /usr/lib/postgresql/11/lib/
ana@ana-pg % sudo chmod a+rwx /usr/share/postgresql/11/extension/
ana@ana-pg % sudo chmod a+rwx /usr/lib/postgresql/11/lib/bitcode/

Now we run make install

ana@ana-pg % make install
/bin/mkdir -p '/usr/lib/postgresql/11/lib'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/postgresql/11/extension'
/bin/mkdir -p '/usr/share/doc/postgresql-doc-11/extension'
/usr/bin/install -c -m 755 oracle_fdw.so '/usr/lib/postgresql/11/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/usr/share/postgresql/11/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/postgresql-doc-11/extension/'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode/oracle_fdw'
/bin/mkdir -p '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/
/usr/bin/install -c -m 644 oracle_fdw.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_utils.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
/usr/bin/install -c -m 644 oracle_gis.bc '/usr/lib/postgresql/11/lib/bitcode'/oracle_fdw/./
cd '/usr/lib/postgresql/11/lib/bitcode' && /usr/lib/llvm-6.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o oracle_fdw.index.bc oracle_fdw/oracle_fdw.bc oracle_fdw/oracle_utils.bc oracle_fdw/oracle_gis.bc

Now we can create an extension in the test database of postgresql-11. For that we login again into psql

ana@ana-pg ~ % sudo -i -u postgres
[sudo] password for ana: 
postgres@ana-pg:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=# \c testdb;

Now we create an extension using the following:

testdb=# create extension oracle_fdw;
CREATE EXTENSION

To check if postgres has installed the oracle_fdw successfully, log into psql and type

testdb=# select oracle_diag();

oracle_diag
oracle_fdw 2.1.0, PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1), Oracle client 12.2.0.1.0
(1 row)

After installing oracle_fdw, we now get hands on and access a table in the oracle schema.

Back to firststeps after installing postgres Import Oracle Foreign Server

First Steps after installing Postgres-11 on Ubuntu 18.04

After installation we need to login to postgresql to create database, roles, schemas etc

When postgresql11 is installed, there is a default superuser postgres created, which does not have any password. We just login to the database using this superuser

sudo -i -u postgres

Now we can enter into psql, which is the database commandline

postgres@ana-pg:~$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.

Now in the command line of psql, we create a database

create database testdb;

To see the list of databases installed:

\list

To quit psql type

\q

Now we create another superuser other than postgres

postgres@ana-pg:~$ createuser --interactive
Enter name of role to add: rtam
Shall the new role be a superuser? (y/n) y
postgres@ana-pg:~$ alter user rtam with password 'partha';

With this new superuser rtam one can add a new server in pgAdmin4. Here we just need to click on “Add New Server”. Then name the server anything. Under Connections Tab, hostname is localhost. 

The username and password are that of the new user we created rtam

The Add New Server will not work with user as postgres, because postgres is created by default in Ubuntu without a password. Now one can see in pgAdmin4 that the database testdb has been created.

back to installing postgres          Installing oracle_fdw

Installing Postgresql-11 on Ubuntu 18.04

Steps involved in installing Postgresql-11 on Ubuntu 18.04.02 are:

We get the package to be installed from the APT repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update

You might get an error message, about missing public key, that looks like this: The following signatures couldn’t be verified because the public key is not available: NO_PUBKEY 7FCC7D46ACCC4CF8

Solution:

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 7FCC7D46ACCC4CF8
sudo apt-get update

Now we can install Postgresql Database and the pgadmin tool that is a graphical tool for database administration.

sudo apt-get install postgresql-11 pgadmin4

Now we setup the database -> First Steps to Setup Database