OnlineSkillBuilder for Postgresql DBA

  Cloning a database is very easy in PostgreSQL. Use the below statement to clone the database. CREATE DATABASE TEMPLATE = ; Give the database name you want to clone in the place of Source database. PostgreSQL will take the database as a template and copy all the objects in that database to new database. Example :- postgres=# create database source; CREATE DATABASE postgres=# \c source You are now connected to database “source” as user “postgres”. source=# create table source(id text); DEBUG:  building index “pg_toast_17241_index” on table “pg_toast_17241” CREATE TABLE source=# insert into source values(‘SOURCE DATABASE’); INSERT 0 1 source=# source=# create database TARGET TEMPLATE=source; CREATE DATABASE source=# \c target You are now connected to database “target” as user “postgres”. target=# target=# \d          List of relations  Schema |  Name  | Type  |  Owner ——–+——–+——-+———-  public | source | table | postgre...

  pg_controldata A  server utility  to show global state information for a database cluster pg_controldata  is a  server utility  which displays global state information about a  database cluster  stored in the  global/ pg_control  file. This information includes cluster characteristics fixed during the  initdb  process such as blocksize as well as  WAL  /  checkpoint -related data and  data page checksums . pg_controldata  is included with the PostgreSQL core distribution; prior to  PostgreSQL 7.3  it was a  contrib module . SQL alternatives From  PostgreSQL 9.6  the contents of  pg_control  can be directly queried with the following functions: pg_control_checkpoint()   (information about the current checkpoint state) pg_control_system()   (information about the current control file state) pg_control_init()   (information about the cluster initializatio...

What is pg_cron   : -   pg_cron is a simple cron-based job scheduler for   PostgreSQL (9.5 or higher)   that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database . Why We need it ? Running periodic maintenance jobs or removing old data is a common requirement in PostgreSQL. A simple way to achieve this is to configure cron or another external daemon to periodically connect to the database and run a command. Let's see how it's works  Step 1 :-  For implementing/Installation of pg_cron you need to download source code from git Dowload link  export PATH=/usr/local/pgsql/bin:$PATH wget https://github.com/citusdata/pg_cron/archive/master.zip unzip master cd pg_cron-master/ make make install    Step 2 : - To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to  shared_preload_libraries   in post...

  Filesystem Structure of Redhat Linux 8 :- The  PostgreSQL Yum Repository  will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support  lifetime  of PostgreSQL. The PostgreSQL Yum Repository currently supports: Red Hat Enterprise Linux Rocky Linux CentOS Scientific Linux Oracle Linux Fedora * * Note : due to the shorter support cycle on Fedora, all supported versions of PostgreSQL are not available on this platform. We do not recommend using Fedora for server deployments. To use the PostgreSQL Yum Repository, follow these steps: Last login: Tue Jul 27 08:14:33 2021 [root@localhost ~]# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm Updating Subscription Management repositories. Last metadata expiratio...

  Introduction PostgreSQL  is an open-source, object-relational database system with a strong reputation for feature robustness, extensibility, and technical standards compliance. The latest version of this database system is  PostgreSQL 12.1 , while versions 11.6, 10.11, 9.6.16, 9.5.20, and 9.4.25 still get regular support updates. PostgreSQL is one of the most famous open-source, freely available database management software systems out there. This system is quick, reliable, flexible, easy to use, and contains advanced features that allow complex applications to be built in a fault-tolerable workspace. There are two different packages of PostgreSQL, each intended for a specific purpose. The PostgreSQL Client package works on the client side to connect to servers, while the PostgreSQL Server package allows your system to set up and host your own databases. This tutorial will show you how to install these two packages and how to set up the PostgreSQL server in a few easy ...