PostgreSQL 9.4 – Database dumps and Restores

Create a Compressed dump file of a Remote Database and Restore it

  • Create a highly compressed data dump
pg_dump -h -U seeker -C -c -v -F c -d servers -f data_dump.dump
  • Restore the data dump you created
pg_restore -C -v -U seeker -d servers -h  data_dump.dump

Create SQL data dumps and Restore them

  • Create a SQL Schema only dump
pg_dump -U  seeker  -d servers   -h --schema-only -f schema_only.sql
  • Create a SQL Data only dump
pg_dump -U  seeker  -d servers   -h --data-only -f data_only.sql
  • Restore SQL data dump using psql
\i "file_name".sql
     Important Notes about Backing up and Restoring

The database, schema and the role must be first created.

Back up and Restore specific tables

  • Backup a specific table
 pg_dump -Fc -v -U seeker -d servers -t a_table > /tmp/a_table.pgdump
  • View the tables of a data dump
pg_restore -l  data_dump_file.dump
  • Extract a table from a dump file
pg_restore -U seeker --data-only --table=a_table database.pgdump >
  • Upload the data of this table to a table in the database
 psql -U user_name -d database_name <
Share Button

Python – PSQL postgresql Client check and installer.

The script performs a check to see if PSQL client is installed. If it is not installed then it will check if the postgres-contrib RPM package is installed and if not it will go ahead and install it. Then regardless it will continue with the installation of the PSQL package. Note: The script does not know how to handle SSH not being up on the remote server. It will quit if SSH is not running on a server.

The script can be run by issuing the following command

$ fab -f check_PSQL 
from fabric.api import *
from fabric.api import settings
from fabric.api import run, roles
import socket
env.roledefs = {
    'db': ['', ''],
    'web': ['web1', 'web2', 'web3'],
env.use_ssh_config = False
env.user = "root"
env.password = "ILoveCake!"
env.port = 22
hostname = socket.gethostname()
#Function that checks if PSQL is installed.
def check_PSQL():
    with settings(abort_exception = Exception):
        try: #Check if psql is installed. If it is exit function and execute post_install_check_PSQL function
            if run("which psql", warn_only = True).succeeded:
            else: #If it is not installed it will check to see if psql94 yum RPM package is installed
                if run("rpm -q postgresql94-contrib").succeeded:
                    run ("yum install -y postgresql94-contrib") #if it is installed then it will install the PSQL package
        except Exception: #if psql94 yum RPM package is NOT installed it will install it and then procceed with the install of PSQL
            run ("rpm -Uvh")
            run ("yum install -y postgresql94-contrib")
def post_install_check_PSQL(): #This function performs a post install check. It checks if PSQL is installed or not.
                               #If PSQL is not installed then it will append to a list the name fo the server and display the server name
                               #with the failure.
    with settings(abort_exception = Exception):
            run("which psql", warn_only = True).succeeded
            print "PSQL is installed"
        except Exception:
            print "Issue with PSQL instal on following server"
            print check_PSQL_hosts_error
Share Button

How to upgrade Postgresql from 8x to 9.2.6 – Centos 5.9 and Centos 6.4

These upgrades retain the old database engine while installing the new version as well. However, in order to have both engines running you will need to change the port on which each engine is listening to. Remember, two applications cannot use the same port over the network for incoming connections.  The directory structure of the Postgresql 8x is

  • /var/lib/pgsql/data

Once you upgrade to version 9.2.6 the directory structure for your new engine will be

  • /var/lib/pgsql/9.2
Share Button