Read-only user in PostgreSQL

 

As a Database Administrator, we always classify various types of schema with specific privileges. In the standard application, there are a few various types of Database Users like Application user, Read-only user, Admin user.

PostgreSQL Ora2Pg : Migrate Oracle to Postgres

Ora2Pg is Free Software written and maintained by Gilles Darold. Its an Ideal for large migration projects.

 

3 Reasons to move to PostgreSQL :

• Reduce Infra & Business Costs
• Reduction of license costs to ZERO
• Reduction of support costs by 80% or more

PostgreSQL : Get total pages of an object

Below query can be used

SELECT pg_relation_size(object_id, 'main')/8192);

PostgreSQL Startup Failure : FATAL: could not access file "pg_stat_statements"

 I tried to enable the pg_stat_statements and Database was unable to start 

PostgreSQL 13 Released ! Everything you need to know about v13 and MySQL vs PostgreSQL

 The major highlights:

 PostgreSQL 13 contains many new features and enhancements, including:

Where **not** to use PostgreSQL?

 

Postgres has made great strides in adding features to match proprietary databases, and it has many complex features that other databases don't have.

All about database Upgrade and troubleshooting (using pg_upgrade)

In this article, we will upgrade PostgreSQL 9.6 to 10. PostgreSQL is evolving day by day and every new release comes with different features. One of the features of PostgreSQL 12 is the optimization of read/write performance in the B-tree index.

All about Physical Replication and Log shipping in Postgres

 In my previous post, I tried to explain how we can configure the replication step by step. Today, I thought I will mention a few things about how it actually works.

 

Talk about pgcenter utility - what is it, how to use it, how to find problems related with postgresql performance

 

PgCenter It's well known that PostgreSQL has rich activity statistics that allow a better understanding of its internal processes. Top-like interface for viewing stats. System resource utilization (cpu, memory, storage, networking). PostgreSQL general utilization (connections, autovacuum, qps) Common admin tasks.

Configure Streaming Replication in PostgreSQL

By default, Streaming replication is asynchronous meaning, once a transaction is committed on the primary, there is a slight delay when that same transaction is committed, and written on the replica. There is potential for data loss with this type of configuration.

Install PgBadger in Linux and set configurations

Untar the file and make a Perl file

#tar -zvxf pgbadger-4.1.tar.gz 
#cd pgbadger-4.1
#perl Makefile.PL
WARNING: META_MERGE is not a known parameter.
Checking if your kit is complete...
Looks good
'META_MERGE' is not a known MakeMaker parameter name.
Writing Makefile for pgBadger

FATAL: requested timeline 3 is not a child of this server's history

When we perform a switch-over, the replica fails to start with below errors.

Switchover - master server and warm standby (with streaming replication) in PostgreSQL

Switch-over of a master database to replica is pretty easy. Lets see how we do it.. 

 I have 2 servers : 5522(data_replica2) and 5432(data), and at this time, 5522 is the master server and 5432 is a warm standby with streaming replication. 

pg_dump: aborting because of server version mismatch

Initiating backup using pg_dump keeps on failing with error as below :

pg_dump: [archiver (db)] query was: LOCK TABLE

While performing backup using pg_dump with database user fails with error as below :

pg_restore with -C option fails to create database. FATAL: database "nexaprod" does not exist

When you use pg_restore you have various options available, for example : 

-c to drop database objects before recreating them,

-C to create a database before restoring into it,

initialise multiple postgres instance on same RHEL server

It is pretty easy to install multiple instances of PostgreSQL servers on same server and have its benefits.

Who uses Postgresql

A good read Who uses Postgresql 

Postgres | Query to identify the lag among the Primary and Replica

How to stop starting of clusters while starting up services

Infra activities such as upgrade, OS patching or Sevrer maintaince activity may require disabling the auto startup of any specific cluster or if you want  NOT auto start on a call such as sudo service postgresql start