PostgreSQL Database

like1

Steps and Concepts of Streaming Replication in PostgreSQL

The process of replication in PostgreSQL using continuous shipping of WAL xlogs to slave servers and applying them in order to keep them current, is called streaming replication in PostgreSQL. This feature has been there in Posgre since  version 9.0

The steps to implement streaming replication is as follows:

First we need to setup the primary server

Step1: check the hostname and port as below:

Taking Online Backup in PostgreSQL

(multiple options are available to be called, while taking online backup, as arguments, to suite the need)

        
  1. Ensure that WAL archiving is enabled and working.
  2.     

  3. Connect to the database as a superuser and issue the command:

 

SELECT pg_start_backup(‘label’);

 

Types of Encryption in PostgreSQL

PostgreSQL has different options for encryptions at different levels that fulfil your security requirements encompassing different scenarios and insecure networks.

1. Encrypting Columns in PostgreSQL (Encryption For Specific Columns)
The pgcrypto module provides cryptographic functions for PostgreSQL.
You can apply encryption on a specific column. e.g,;

Copying/ Migrating PostgreSQL to another server (Live)

  1. Using Virtual backups style, i.e., pg_dump or pg_dumpall

The command can be used as below:

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

 

  1. Using Physical backups i.e, pg_basebackup.

Step1: Make sure you have the PosgreSQL is installed on remote server.

Step2: Copy the backup from the original server taken with pg_basebackup.

Steps for Point in time recovery from Base Backup and WAL

1.    Stop the server, if it's running.

2.    If you have the space to do so, copy the whole cluster data directory and any tablespaces to a temporary location in case you need them later. Note that this precaution will require that you have enough free space on your system to hold two copies of your existing database. If you do not have enough space, you should at least save the contents of the cluster's pg_xlog subdirectory, as it might contain logs which were not archived before the system went down.

Setting Up Archiving of transaction logs (WAL) on the PostgreSQL on Linux server

To set up WAL (Write Ahead Log) we first need to understand the following:

Need: To archive the transaction logs before they are overwritten by Postgre

Mechanism: System physically divides the long sequence of WAL records into small WAL segment files. And now before the file segments are overwritten they are archived (by any OS utility)

 

We set WAL level to enable WAL archiving to archive_mode or hot_standby. We edit the postgresql.conf:

Edit postgresql.conf

Applying patch on PostgreSQL on Linux

Step1: Download the patch from

https://www.postgresql.org/download/

Note: Applying the patch requires that you have the source tree downloaded

and can build from there.  If you've installed from a package, then

you'll need to run the pg_config command that comes with your package.

 It's often part of the -dev or -devel package.  That command will

tell you all the switches used to build your package.

Steps and Concepts of Streaming Replication in PostgreSQL

The process of replication in PostgreSQL using continuous shipping of WAL xlogs to slave servers and applying them in order to keep them current, is called streaming replication in PostgreSQL. This feature has been there in Posgre since  version 9.0

The steps to implement streaming replication is as follows:

First we need to setup the primary server

Step1: check the hostname and port as below:

Taking Online Backup in PostgreSQL

(multiple options are available to be called, while taking online backup, as arguments, to suite the need)

        
  1. Ensure that WAL archiving is enabled and working.
  2.     

  3. Connect to the database as a superuser and issue the command:

 

SELECT pg_start_backup(‘label’);

 

Types of Encryption in PostgreSQL

PostgreSQL has different options for encryptions at different levels that fulfil your security requirements encompassing different scenarios and insecure networks.

1. Encrypting Columns in PostgreSQL (Encryption For Specific Columns)
The pgcrypto module provides cryptographic functions for PostgreSQL.
You can apply encryption on a specific column. e.g,;

Copying/ Migrating PostgreSQL to another server (Live)

  1. Using Virtual backups style, i.e., pg_dump or pg_dumpall

The command can be used as below:

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

 

  1. Using Physical backups i.e, pg_basebackup.

Step1: Make sure you have the PosgreSQL is installed on remote server.

Step2: Copy the backup from the original server taken with pg_basebackup.

Steps for Point in time recovery from Base Backup and WAL

1.    Stop the server, if it's running.

2.    If you have the space to do so, copy the whole cluster data directory and any tablespaces to a temporary location in case you need them later. Note that this precaution will require that you have enough free space on your system to hold two copies of your existing database. If you do not have enough space, you should at least save the contents of the cluster's pg_xlog subdirectory, as it might contain logs which were not archived before the system went down.

Setting Up Archiving of transaction logs (WAL) on the PostgreSQL on Linux server

To set up WAL (Write Ahead Log) we first need to understand the following:

Need: To archive the transaction logs before they are overwritten by Postgre

Mechanism: System physically divides the long sequence of WAL records into small WAL segment files. And now before the file segments are overwritten they are archived (by any OS utility)

 

We set WAL level to enable WAL archiving to archive_mode or hot_standby. We edit the postgresql.conf:

Edit postgresql.conf

Applying patch on PostgreSQL on Linux

Step1: Download the patch from

https://www.postgresql.org/download/

Note: Applying the patch requires that you have the source tree downloaded

and can build from there.  If you've installed from a package, then

you'll need to run the pg_config command that comes with your package.

 It's often part of the -dev or -devel package.  That command will

tell you all the switches used to build your package.

After fresh installation of PostgreSQL on Linux Unable to connect to database.

I installed Postgres 9.2 on Linux RHEL 7.3 using PostgreSQL binary. I am unable to connect to the PostgreSQL database. Error:

I installed Postgres 9.2 on Linux RHEL 7.3 using PostgreSQL binary. I am unable to connect to the PostgreSQL database. Error:

$psql postgres
 
psql: Could not connect to the server: No such file or directory
 
              Is the server running locally and accepting
 
              connectioni on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
No wikis have been added to this group.
No polls have been added to this group.

Recent activity in this group

Tue, 5/8/2018 - 6:12pm
Tue, 4/17/2018 - 12:07pm
Tue, 3/20/2018 - 11:53am

Group contributors

Contributors

Organizers