PostgreSQL DBA part 7 :-PostgreSQL full and incremental backup restore using BART 2.0 tool

BART is a backup and recovery tool for EDB Postgres Databases EDB Backup and Recovery Tool (BART) is a key component of an enterprise-level Postgres-based data management strategy.  BART implements retention policies and point-in-time recovery requirements for large-scale Postgres deployments. Now available, Bart 2.0 provides block-level incremental backup. P.C https://www.enterprisedb.com/products/edb-postgres-platform/edb-backup-and-recovery-tool A.Full backup and restore using BARTContinue reading PostgreSQL DBA part 7 :-PostgreSQL full and incremental backup restore using BART 2.0 tool

PostgreSQL DBA part 6 :-PostgreSQL high availability setup using EFM

PostgreSQL high availability setup using EFM   A.Introduction This document provides you the overview of High Availability Setup for EDB Postgres 9.5 database. An EDB Postgres Failover Manager (EFM) cluster is comprised of Failover Manager Processes that reside on the following hosts on a network: A Master node – The Master node is the primaryContinue reading PostgreSQL DBA part 6 :-PostgreSQL high availability setup using EFM

PostgreSQL DBA part 5 :- Create user and grant privilege in postgresql

edb=# create user test identified by test; CREATE ROLE edb=# grant connect on database edb to test; GRANT edb=# grant usage on schema public to test; GRANT edb=# create table t(c1 char(10)); CREATE TABLE edb=# insert into t values(‘DEB’); INSERT 0 1 edb=# commit; COMMIT edb=# grant select,insert on emp to test; GRANT edb=# \cContinue reading PostgreSQL DBA part 5 :- Create user and grant privilege in postgresql

PostgreSQL DBA part 4 :-How to create database in postgresql

How to create database in postgresql Create Database command can be used to create a database in a cluster. −Syntax: Creating Databases CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner][ TEMPLATE [=] template ][ ENCODING [=] encoding ][ TABLESPACE [=] tablespace][ CONNECTION LIMIT [=] connlimit ] ] 1.Create database in postgresql  namedContinue reading PostgreSQL DBA part 4 :-How to create database in postgresql

PostgreSQL Parameters(The following are most common parameters in postgresql.conf)

A.Many Ways to set postgresql parameters •Some parameters can be changed per session using the SETcommand. •Some parameters can be changed at the user level using ALTER USER. •Some parameters can be changed at the database level using ALTER DATABASE. •The SHOW command can be used to see settings. •The pg_settings and pg_file_settings catalog tableContinue reading PostgreSQL Parameters(The following are most common parameters in postgresql.conf)

PostgreSQL DBA part 3 :-How to start/stop postgresql database

A.Stopping postgresql database pg_ctl can be used to stop a database cluster •pg_ctl supports three modes of shutdown −smart quit after all clients have disconnected −fast (default)quit directly, with proper shutdown −immediate quit without complete shutdown; will lead to recovery •Syntax −pg_ctl stop [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] -bash-4.1$ pwd /opt/edb/as9.6/bin -bash-4.1$Continue reading PostgreSQL DBA part 3 :-How to start/stop postgresql database

PostgreSQL DBA part2 :-How to connect postgresql database

A.Connecting postgresql locally using port Please identify port in postgresql.conf under /opt/edb/as9.6/data and connect using psql There are pre-requisites for allowing local connection.This pg_hba.conf file is under  /opt/edb/as9.6/data.Please make sure last parameter should be md5 not peer. The below content is for example what should be in pg_hba.conf # “local” is for Unix domain socket connections onlyContinue reading PostgreSQL DBA part2 :-How to connect postgresql database

PostgreSQL DBA part1 :-Installation of PostgreSQL 9.6 in Linux

I will show how you can install PostgreSQL  9.6 in Linux 7.This document is based on guidance provided by EDB (Enterprise distributor of PostgreSQL) Step 1:-First we need to check Supported platform as below The Advanced Server 9.6 RPM packages are supported on the following platforms: 64 bit Linux: Red Hat Enterprise Linux 6.x andContinue reading PostgreSQL DBA part1 :-Installation of PostgreSQL 9.6 in Linux

PostgreSQL Database Backup to S3 and Restore from S3 using simple shell script

Following topic will discuss how to Backup and Restore PostgreSQL Databases directly in S3. This topic will help you to leverage AWS S3 -IA storage to use as Backup Storage for any number of PostgreSQL databases running under an instance. This topic will cover PostgreSQL Instance running on EC2 or on-premises servers. Step 1:- Create aContinue reading PostgreSQL Database Backup to S3 and Restore from S3 using simple shell script