PostgreSQL Database

like1

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

Taking Base Backup in PostgreSQL

We can use the simple command to take base backup as below:

Pg_basebackup[options..];

(multiple options are available to be called here as arguments, to suite the need)

The procedure for making a base backup. It is very important that these steps are executed in sequence, and that the success of a step is verified before proceeding to the next step.

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.

Basic PostgreSQL psql Commands

BASIC SLASH COMMANDS (\?)

  1. List of databases

\l

 

  1. Connect to new database

\c database_name

 

  1. View list of tables/ Relations

\dt

 

  1. Describe the details of given table

\d table_name

 

  1. Seek help regarding SQL command syntax

\h

 

  1. List pgsql slash (“\”) commands

\?

 

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"?

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

Taking Base Backup in PostgreSQL

We can use the simple command to take base backup as below:

Pg_basebackup[options..];

(multiple options are available to be called here as arguments, to suite the need)

The procedure for making a base backup. It is very important that these steps are executed in sequence, and that the success of a step is verified before proceeding to the next step.

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.

Basic PostgreSQL psql Commands

BASIC SLASH COMMANDS (\?)

  1. List of databases

\l

 

  1. Connect to new database

\c database_name

 

  1. View list of tables/ Relations

\dt

 

  1. Describe the details of given table

\d table_name

 

  1. Seek help regarding SQL command syntax

\h

 

  1. List pgsql slash (“\”) commands

\?

 

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.