CLONE DATABASE IN POSTGRESQL

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 | postgres
(1 row)

target=# select * from source;
id
—————–
SOURCE DATABASE

Comments

PostgreSQL Vacuum and Vacuum full are not two different processes

PostgreSQL’s VACUUM and VACUUM FULL are not separate processes but rather different operational modes of the same maintenance command. Here’s why: Core Implementation Both commands share the same underlying codebase and are executed through the vacuum_rel() function in PostgreSQL’s source code ( src/backend/commands/vacuum.c ). The key distinction lies in the FULL option, which triggers additional steps: Standard VACUUM : Removes dead tuples (obsolete rows) and marks space reusable within PostgreSQL Updates the visibility map to optimize future queries Runs concurrently with read/write operations VACUUM FULL : Rewrites the entire table into a new disk file, compressing it and reclaiming space for the operating system Rebuilds all indexes and requires an ACCESS EXCLUSIVE lock, blocking other operations Key Differences in Behavior Aspect Standard VACUUM VACUUM FULL Space Reclamation Internal reuse onl...

Job scheduler for PostgreSQL "pg_cron"

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...

PostgreSQL Health Check - Performance Audit and Recommendations

PostgreSQL Server Capacity Planning and Optimization: Conduct a thorough assessment of current and projected workload demands on the database server's critical resources. This evaluation should cover key factors such as CPU utilization, RAM consumption, storage requirements, I/O performance, and network bandwidth usage. Consider both short-term fluctuations and long-term usage patterns to accurately forecast future needs and potential bottlenecks. Collect and analyze performance metrics over extended periods, including peak and off-peak hours, to gain a comprehensive understanding of server behavior. Use appropriate monitoring tools to gather detailed data on query execution times, resource utilization patterns, and system responsiveness under varying loads. Identify recurring performance issues, resource contention points, or capacity limitations that may affect the overall efficiency of the PostgreSQL environment. Based on this analysis, develop actionable recommendations...

OnlineSkillBuilder for Postgresql DBA

Search This Blog