Like other RDBMSes, the world’s most advanced opensource database PostgreSQL also comes with a wide range of backup solutions Logical & Physical. Logical backup is a method of dumping the database into single or multiple files which can be restored to the time of backup taken using a dump file. On the other hand, Physical backup in PostgreSQL is a backup of the base directory where database files are stored, this backup is useful for crash recovery and to perform […]
PostgreSQL supports most of the major features of SQL:2003. Out of 164 mandatory features required for full Core conformance, PostgreSQL conforms to at least 150. No current version of any database management system claims full conformance to Core SQL:2003. Below are some PSQL (Postgres’s SQL) commands for beginners to getting started with PostgreSQL. 1. Connecting to PostgreSQL database server. psql -U username -h hostname -d databasename e.g. These arguments can be exported in environment variables similar to above connection commands
PostgreSQL maintains Write Ahead Logging to keep records of transactions of database server. And keeps WALs in pg_xlog (Version 9.x and earlier) / pg_wal (Version 10.x and later) directory. However these WALs remains temporarily in this directory and overwrites WALs in Round Robin fashion depending upon configured wal_keep_segments parameter in postgresql.conf file. But these WALs can be archived to different permanent location where it will never be overwritten. Archived WALs plays major role in crash recovery or Point In Time
PostgreSQL can be installed using yum, RPM packages or by compiling source code. I have already covered basic installation using yum. In this post I will going to cover Source Code based installation. So let’s begin. Install prerequisite packages. Make sure you have directory structure (Either on OS Mountpoint or Dedicated Mountpoint) for binaries and data files. I have dedicated mountpoints for Binaries (/pg_bin), for Database Cluster (/pg_data), for WAL Archives(/archive) and for Backup (/backup) as listed below. Check these
Step By Step Streaming Replication In PostgreSQL 12 In this demonstration,We’ll see how to setup a Streaming Replication in PostgreSQL 12 running on Oracle Linux 6.8 Here, I assume that you have two servers (Primary/Master & Standby/Slave) having PostgreSQL 12 installed. And Database cluster is initialized using initdb on both the servers. If not, you might want to see Installation of PostgreSQL here. Note that you don’t have to start the database server, just follow till initdb from this article.
PostgreSQL 12 on Oracle Linux 7.5 There are various methods available to install PostgreSQL database on your Linux server, however yum is one of the always preferred methods when it comes to RedHat Family Distributions.As this demonstration is done on Oracle Linux 7.5 you can proceed as it is for the installation on Oracle Linux 7.x RedHat Enterprise Linux 7.x & CentOS 7.x. If you’re planning to install it in anyother distribution or version, you may want to visit PostgreSQL