Physical PostgreSQL Backup using pg_basebackup pg_basebackup is used to take base backups of a running PostgreSQL database cluster. It Can be used both for point-in-time recovery and as the starting point for log shipping or streaming replication standby servers. pg_basebackup makes a binary copy of the database cluster files while making sure the system is
Physical PostgreSQL Backup Is file system-level copy of files that comprise the database cluster can be either Offline or Online mode. Advantage : 1) Generally faster to backup and restore. 2) Allow Point-In-Time-Recovery PITR (If WAL are archived) 3) Full or Incremental backup. 4) Will allow DDL during backup. Disadvantage : 1) Lack of flexibility:- Backup/Restore entire DB Cluster. Selective
Logical PostgreSQL Backup – Import Logical (SQL DUMP) Database Backup (Export/Import) is an extract of some or all data or structure of objects within the database. This is a dump in the form of an SQL command that can be executed on the server in order to recreate database objects. Advantage:1) Flexibility: Dump files are OS-independent. 2) Cross Version Compatibility: Take backup from Higher Version and
Logical PostgreSQL Backup – Export Logical (SQL DUMP) Database Backup is an extract of some or all data or structure of objects within the database. This is a dump in the form of an SQL command that can be executed on the server in order to recreate database objects. Advantage:1) Flexibility: Dump files are OS-independent. 2)
Similarities Between Oracle & PostgreSQL In this blog, we will compare similarities between Oracle & PostgreSQL in terms of Memory, Process, Files, General architecture, etc. COMPONENTS ORACLE POSTGRESQL Architecture: Memory Shared Global Area (SGA) Shared Memory DB buffer cache Shared buffer Log Buffer WAL (Write Ahead Log) Buffer Private Global Area (PGA) Work Memory Architecture:
Tablespaces in PostgreSQL A tablespace allows superusers to define an alternative location on the file system where the data files containing database objects (such as tables and indexes) can reside. The tablespace name must be distinct from the name of any existing tablespace in the database cluster. PostgreSQL uses a tablespace to map a logical
Vacuum and Autovacuum in PostgreSQL In this article, we will understand the detailed concept of Vacuum & Autovacuum along with practical examples of the same. Vacuum Whenever we run an UPDATE command, the system does not change the actual data, but it inserts new data in reference to the old data and mark the row
Configure WAL Archiving in PostgreSQL WAL is short for Write Ahead Log. In PostgreSQL terms, copying out generated WAL files is called archiving,If you save a copy of each WAL file that was generated, you could replay back the entire set of changes to another server. Saving all generated WAL files to a safe offline
Postgresql Architecture – Physical Storage Structure In this blog, we will discuss PostgreSQL architecture of physical storage which can be categorized in below four part: 1. Data Directory 2. Physical Files 3. Data file layout (OID) 4. Database Structure 1. Data Directory Specifies the directory to use for data storage. This parameter can only be
Postgresql Architecture – Process In the previous blog, we looked at Memory Components in PostgreSql, Click here to read more. In this post we will discuss the Process of PostgreSQL, Processes in PostgreSQL can be divided mainly into 4 types: 1. Postmaster (Daemon) Process 2. Background (Utility) Process 3. Backend Process 4. Client Process 1.