POSTGRESQL

                               Postgresql for Owncloud

 

Here we have used streaming replication and it is used for master-salve replication . There is no master-master or clustering feature available with postgresql SR (streaming replication).

 

The way SR works is that there are log files (known as XLOG files) which are shipped to the standby or slave server via network. Multiple slave servers can connect to the master over the network. The stand by servers continuously replay the XLOG records shipped in continuous recovery mode.As soon as XLOG files are shipped, they are replayed on the slave. This makes latest data available on slave almost immediately

 

Here is how I did a  setup of master-slave replication using postgresql between owncloud1 and owncloud1

 

I have  2 machines 172.16.1.90 and 172.16.1.91. I downloaded postgresql-9.5.0.tar.bz2 on both.

 

For installing in UBUNTU server please install the following devel

apt-get install libreadline-dev

apt-get install libssl-dev

apt-get install libreadline5-dev

apt-get install libperl-dev

apt-get install libsdl-perl

apt-get install zlib1g-dev

 

POSTGRES INSTALLATION

 

root@owncloud1:~# groupadd -g 1090 postgres

root@owncloud1:~# useradd -g 1090 -u 1090 -s /bin/bash -d /home/postgres postgres

root@owncloud1:~# mkdir -p /home/postgres

root@owncloud1:~# chown -R postgres:postgres /home/postgres

root@owncloud1:~# passwd postgres

Enter new UNIX password:

Retype new UNIX password:

passwd: password updated successfully

root@owncloud1:~# su – postgres

postgres@owncloud1:~$ ls

postgres@owncloud1:~$ mkdir downloads tmp bin sbin

postgres@owncloud1:~/downloads$

wget https://ftp.postgresql.org/pub/source/v9.3.6/postgresql-9.3.6.tar.bz2

postgres@owncloud1:~/downloads$ tar -jxvf postgresql-9.3.6.tar.bz2

postgres@owncloud1:~/downloads$ cd postgresql-9.3.6/

postgres@owncloud1:~/downloads/postgresql-9.3.6$ ./configure –prefix=/home/postgres/pgsql-9.3.6/ –enable-thread-safety –with-perl –with-openssl

postgres@owncloud1:~/downloads/postgresql-9.3.6$ make

postgres@owncloud1:~/downloads/postgresql-9.3.6$ make install

postgres@owncloud1:~/downloads/postgresql-9.3.6$ cd contrib/

postgres@owncloud1:~/downloads/postgresql-9.3.6/contrib$ make

postgres@owncloud1:~/downloads/postgresql-9.3.6/contrib$ make install

postgres@owncloud1:~/downloads/postgresql-9.3.6/contrib$ cd xml2/

postgres@owncloud1:~/downloads/postgresql-9.3.6/contrib/xml2$ make

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/initdb -D data-9.3.6

 

PGPOOL INSTALLATION

 

postgres@owncloud1:~/downloads$ wget http://www.pgpool.net/download.php?f=pgpool-II-3.3.1.tar.gz

postgres@owncloud1:~/downloads$ tar -zxvf download.php?f=pgpool-II-3.3.1.tar.gz

postgres@owncloud1:~/downloads$ cd pgpool-II-3.3.1/

postgres@owncloud1:~/downloads/pgpool-II-3.3.1$ ./configure –prefix=/home/postgres/pgpool –with-openssl

postgres@owncloud1:~$ make

postgres@owncloud1:~$ make install

 

For PGPOOL

 

root@owncloud1:~# mkdir -p /home/postgres/sbin

root@owncloud1:~#chown -R postgres:postgres /home/postgres/sbin

root@owncloud1:~# chmod 700 /home/postgres/sbin

root@owncloud1:~#cp /sbin/ifconfig /home/postgres/sbin

root@owncloud1:~#cp /user/sbin/arping /home/postgres/sbin

          

root@owncloud1:~#apt-get install arping

root@owncloud1:~#which arping

root@owncloud1:~#cp /usr/sbin/arping /home/postgres/sbin

root@owncloud1:~#chmod 4755 /home/postgres/sbin

root@owncloud1:~#chmod 4755 /home/postgres/sbin/arping

root@owncloud1:~#chmod 4755 /home/postgres/sbin/ifconfig

 

Configure Postgresql Master Server

 

configure master server to listen on all ip addresses.

#vi  /home/postgres/data-9.3.6postgresql.conf

listen_addresses = ‘*’

 

Allow standby server to connect to postgresql on master with replication privilege also allow to connect to database from remote machine

 

#vi /home/postgres/data-9.3.6pg_hba.conf

host   replication   postgres 172.16.1.91/24 trust

host    all             all             172.16.1.0/24            trust

 

Setup replication related parameters in the master server

#vi /home/postgres/data-9.3.6postgresql.conf

 

# 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 = 128

 

  # 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 = ‘rsync -a %p postgres@172.16.1.91:/home/postgres/archive_xlogs/%f </dev/null’

 

start postgresql on master

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/pg_ctl -D /home/postgres/data-9.3.6 -l /home/postgres/log start

 

Copy the master server’s data to standby server

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/psql -c “SELECT pg_start_backup(‘label’, true)”

Pg_start_backup

 

0/4000020

(1 row)

 

postgres@owncloud1:~$  rsync -a /home/postgres/data-9.3.6/* postgres@172.16.1.91:/home/postgres/data-9.3.6/ –exclude postmaster.pid

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/psql -c “SELECT pg_stop_backup()”

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

pg_stop_backup

—————-

0/40000D8

(1 row)

 

This will also copy all the configuration parameters and authentication related stuff from primary to standby slave.

Ensuring that the slave can be converted to a master/primary in case of a failover.

 

ON standby Slave server

 

Change postgresql.conf to enable readonly queries on standby server

postgres@owncloud2:~$ vi /home/postgres/data-9.3.6/postgresql.conf

 hot_standby = on

 

 

Enable recovery on the standby server and change configuration.

 

postgres@owncloud2:~/data-9.3.6$ vi recovery.conf

 

# Specifies whether to start the server as a standby. In streaming replication,

   # this parameter must to be set to on.

   standby_mode       = ‘on’

 

# Specifies a connection string which is used for the standby server to connect

   # with the primary.

primary_conninfo   = ‘host=172.16.1.90 port=5432 user=postgres’

 

# Specifies a trigger file whose presence should cause streaming replication to

   # end (i.e., failover). Once the trigger file is found the server acts as a primary server.

  trigger_file = ‘/home/postgres/archive_xlogs/trigger_file’

 

# Specifies a command to load archive segments from the WAL archive. If

   # wal_keep_segments is a high enough number to retain the WAL segments

   # required for the standby server, this may not be necessary. But

   # a large workload can cause segments to be recycled before the standby

   # is fully synchronized, requiring you to start again from a new base backup.

 restore_command = ‘cp /home/postgres/archive_xlogs/%f %p’

 

Start postgres on standby server. This will start streaming replication on the standby server.

postgres@owncloud2:~$ /home/postgres/pgsql-9.3.6/bin/pg_ctl -D /home/postgres/data-9.3.6 -l /home/postgres/log start

 

You can check the status of streaming replication using either the ps command or through psql – postgresql command prompt

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/psql -c “SELECT pg_current_xlog_location()”

pg_current_xlog_location

————————–

0/5000EC0

(1 row)

 

postgres@owncloud2:~$/home/postgres/pgsql-9.3.6/bin/psql -c “select pg_last_xlog_receive_location()”

pg_last_xlog_receive_location

——————————-

0/5000EC0

(1 row)

 

postgres@owncloud2:~$/home/postgres/pgsql-9.3.6/bin/psql -c “select pg_last_xlog_replay_location()”

pg_last_xlog_replay_location

——————————

0/5000EC0

(1 row)

 

To check using ps use the following commands

 

postgres@owncloud1:~$ ps ax | grep sender

9589 ?     Ss 0:00 postgres: wal sender process postgres owncloud2.juhu.zedo.com(57679) streaming 0/6000528

9743 pts/1 S+ 0:00 grep sender

 

postgres@owncloud2:~$ ps ax | grep receiver

23991 ?     Ss 0:00 postgres: wal receiver process   streaming 0/6000528

 

To do a failover, all that needs to be done is to create the ‘trigger’ file at the specified location. This will automatically turn off standby mode and the postgres server will start acting as a primary or master.

 

Do remember to use the ” /home/postgres/pgsql-9.3.6/bin/pg_ctl -D  data-9.3.6 stop” command to stop either the primary or standby server. This will ensure graceful shutdown and no records will be missed being replicated.

 

How to switch Back?

 

After some downtime or maintenance period, your master node is back up again and you want to do switchback. You are going to first turn this node into a standby. In this mode it will catch up with the current MASTER replicating the changes that took place while it was down. So we refer to it as current STANDBY now.

Peform Base backup from current MASTER to current STANDBY

 

PGDATA=”/home/postgres/data”

BACKUP_LABEL=”base-backup”

psql -p $PORT -c “select pg_start_backup(‘$BACKUP_LABEL’);”
rsync -cva –inplace –exclude=*pg_xlog* $PGDATA/ <IP_OF_OTHER_HOST>:$PGDATA/
psql -p $PORT -c “select pg_stop_backup();”

 

Create recovery.conf in $PGDATA on current STANDBY

 

standby_mode = ‘on’
primary_conninfo = ‘host=<IP_OF_OTHER_HOST> port=$PORT user=replicator password=changeme’
trigger_file = ‘/tmp/postgresql.trigger.$PORT’

 

After the catch up is over, you can turn the current STANDBY into MASTER by following above switchover procedure – touch the trigger file.

 

How to stop posgresql fast shutdown?

 

kill -INT `head -1 /home/postgres/data-9.3.6 postmaster.pid`

 

How to connect to postgres master from remote and build  owncloud server database during the setup?

 

php occ db:convert-type –port=”5432″ –password=”password” –clear-schema –all-apps pgsql owncloud 172.16.1.90 owncloud

 

How to check the status of the psql master or slave server?

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/pg_ctl -D data status

 

How to test remote connection of postgres master server?

 

# psql -U postgres -h 172.16.1.90

 

How to stop postgresql gracefully?

 

#/home/postgres/pgsql-9.3.6/bin/pg_ctl -D data stop

 

How to create database in postgresql?

 

/home/postgres/pgsql-9.3.6/bin/createdb test

 

How to login to postgresql database ?

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/psql -U owncloud

 

How to create user and assign password in postgresql?

 

CREATE USER owncloud WITH PASSWORD ‘password’;

 

How to grant all privileges to user with particular database?

 

GRANT ALL PRIVILEGES ON DATABASE owncloud TO owncloud;

 

How to list all databases in postgrsql?

 

owncloud-> \list

 

How to grant access to tables in postgres

GRANT ALL PRIVILEGES ON TABLE oc_users TO privacyidea;

 

Some Useful Commands.

 

  1. connect to the required db: psql -d databaseName
  2. \dt would return the list of all table in the database you’re connected to.

 

How to drop table

owncloud=# DROP TABLE oc_users_external;

DROP TABLE

 

How to switch the server from standby to master.

 

After some downtime or maintenance period, your master node is back up again and you want to do switchback. You are going to first turn this node into a standby. In this mode it will catch up with the current MASTER replicating the changes that took place while it was down. So we refer to it as current STANDBY now.

Peform Base backup from current MASTER to current STANDBY

 

On the new master (172.16.1.91)

  1. su – postgres
  2. touch trigger.txt in /home/postgres/archive_xlogs/trigger_file
  3. recovery.conf becomes recovery.done
  4. Perform below commands

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/psql -c “SELECT pg_start_backup(‘label’, true)”

Pg_start_backup

 

0/4000020

(1 row)

 

postgres@owncloud1:~$  rsync -a /home/postgres/data-9.3.6/* postgres@172.16.1.91:/home/postgres/data-9.3.6/ –exclude postmaster.pid

 

postgres@owncloud1:~$ /home/postgres/pgsql-9.3.6/bin/psql -c “SELECT pg_stop_backup()”

NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

pg_stop_backup

—————-

0/40000D8

(1 row)

 

On the new slave (172.16.1.90)

  1. create the recovery.conf : cp recovery.done recovery.conf
  2. vi recovery.conf change ip address : primary_conninfo = ‘host=172.16.1.91’’
  3. postgresql.conf and pg_hba.conf are identical to what is configured on the master server.
  4. start postgresql

 

Contents of recovery files.

standby_mode          = ‘on’

primary_conninfo      = ‘host=owncloud4.juhu.zedo.com user=postgres’

trigger_file = ‘/home/postgres/archive_xlogs/trigger_file’

restore_command = ‘cp /home/postgres/archive_xlogs/%f %p’

 

Advertisements