Understanding the hierarchy of objects within PostgreSQL can help you avoid confusion as you get to know the system and read up on documentation. PostgreSQL’s main “global” object is a database cluster, which is just the name given to the collection … Read More
Uncategorized
Vacuum and Vacuum Full PostgreSQL
VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables. Plain VACUUM (without FULL) … Read More
Tags: exclusivelock, vacuum, vacuumfull
Exception Handling in PostgreSQL
By default, any error occurring in an SPL program aborts execution of the program. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION section. The syntax is an extension of the normal syntax for a BEGIN block: How it … Read More
PostGIS exercise : part 1
here we will have several question : suppose we have 5 points (a, b, c, d, e), each having it’s own latitude and longitude, consider we have another point (x) on another latitude and longitude, find the nearest and the … Read More
Import CSV file to PostgreSQL
In this session, We will give an example how to import csv file into postgresql table, first, create a new table called nyc_census_blocks_test Second, prepare the csv file, my path of csv file is : /var/lib/pgsql/nyc_census_data.csv To import CSV file … Read More
PostgreSQL Logical Replication
Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication. Logical … Read More
Tags: logical, replication
PostgreSQL using Docker
PostgreSQL, often simply “Postgres“, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data, securely and supporting best practices, and retrieve it later, as requested … Read More
Multi-Master PostgreSQL replication with bucardo
Bucardo is an asynchronous PostgreSQL replication system, allowing for multi-source, multi-target operations. It was developed at Backcountry by Jon Jensen and Greg Sabino Mullane of End Point Corporation, and is now in use at many other organizations. Bucardo is free … Read More
Tags: bucardo, multi-master, replication
Create a symlink (soft link) for different PostgreSQL version
One of a setback of being database administrator is writing a full path for psql command or others. Imagine you have 3 or more postgresql version running in your computer, and for each time you want to connect to database … Read More
Upgrading PostgreSQL 9.5 to 13 using pg_upgrade
pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g., from 8.4.7 to the current major release of PostgreSQL. … Read More
Tags: pg_upgrade, ubuntu