Postgres Frequently used commands - CHEAT SHEET

 \db- List all tablespaces

\dn- List all schemas

Postgresql Database Starts with 100% CPU processes ?

If you have a problem with PostgreSQL, it starts some processes that consume lots of resources. You kill those processes but they will restart after a few minutes.

Later you check the /var/log but there is nothing about this. What does that mean?😟

Oracle : Move indexes to another tablespace

 

Postgres High Availability Terms

 What is High Availability?

It is the amount of time that a service is available and is usually defined by the business.

Oracle Session Kill Script - Session Management

 

Possible ways to recover space from deleted rows with insufficient disk space remaining for full vacuum

 A recent scenario,

We had a single table with about 50M records that use around 80gb of space. Application recently deleted older records which was no longer needed reducing the Number of records to 30M.

What to do if listener log has grown too big in size

 

RMAN Backup Validate

 Sometimes RMAN backup files might get corrupted. Sometimes disk block corruption might happen. So it is better we do validate the database backups more frequently in order to make sure it is recoverable.

Oracle Blocksize selection

I had gone through Oracle documents, forums, and other websites but could not get a clear picture of what blocksize to choose for my environment where 64 bit OLTP databases are running. I came across the following websites that give practical and more useful information about Oracle Block size selection.

http://www.ixora.com.au/tips/buffered_block_size.htm

http://www.nextre.it/oracledocs/blocksize.html

The Surprising Impact of Medium-Size Texts on PostgreSQL Performance

 This is a great explanation of Postgres toast internals for anyone who doesn't know about them 

https://hakibenita.com/sql-medium-text-performance

PostgreSQL : Influencing Query Execution Plan

By enabling and disabling the following parameters, we can influence the query execution plan generated by the optimizer.

MySQL : Troubleshooting Replication

If you have followed the instructions to make a replica (If not have a visit at this quick post), and your replication setup is not working, than the first thing to do is check MySQL error log. (if you dont know where it is? Just check my.cnf for the location of that file). Most of time, you will get the actual error from the log if not than you can try below things.

 

• Check master server and verify that binlog is enable or not and position is rotating or not by using “show master status” command.

• Master and slave server both must have unique server_id.

• Run “show slave status” command on slave server and check below values first.Slave_IO_Running:Slave_SQL_Running: Last_errno: 0 Last_error:

• If “Slave_IO_Running” is “YES” than slave is able to connect master and getting log entries from master but if it’s “NO” that means slave is not able to connect to master so check the values of Master_Host, Master_User, Master_Pass that it is right or not. Also check the connectivity between master and slave for mysql as well as network side.

• If “Slave_SQL_Running” is “YES” than no issue with sql thread butit its “NO” than you should get error on “Last_error”.

• Most of time, you will get the query related error in “Last_error” section.

• If the slave was running previously but stopped suddenly than the reason usually is that some statement that succeeded on the master failed on the slave.

• If a statement that succeeded on the master but refuses to run on the slave, try to do the below things. Because it‘s not feasible to do a full database resynchronization from the masterby copying a new snapshot/backup from the master.

• Sometimes, its happening when table changes are done in master server but somehow its not replicated on slave so slave will refuse to run the statement and give error. In this situation, we have synchronize the changes and start replication with simple “Start Slave” command.

• Sometimes, we are getting some error which can be solve by running that statement manually. At that time we can just stop slave completely, skip the statement with “ set global sql_slave_skip_counter=1” command, start the slave.

ORA-00845: MEMORY_TARGET not supported on this system

Recently, when I tried to increase the memory_target of Oracle database, I got the following error:

ORA-00845: MEMORY_TARGET not supported on this system

While checking, we found that /dev/shm size was 2 GB, but we were trying to increase the memory_target values to 7 GB that is more than the size of /dev/shm.  So we got the error.

Then we increased the /dev/shm size to 8 GB using the command as below

 mount -t tmpfs shmfs -o size=8192m /dev/shm

After that, we were able to bring up the database with the parameter value memory_target =7G without the issue.

Create Extension Fails : ERROR: could not access file "$libdir/dblink"

 

After my recent DB upgrade activity, I was unable to create extension and was failing with the below errors.

Find Uptime in PostgreSQL

 There are various ways to know since how long PostgreSQL is running. 

Below will cover 3 ways to find this.

PostgreSQL Set DB Timezone

If you plan to work with date and time data in PostgreSQL, you’ll want to ensure that you’ve set the correct time zone for your location. Fortunately, it’s easy to make this change using the psql command-line interface. In this article, we’ll explain how to connect to PostgreSQL and set the time zone.

Mysql Database Installation and Administration

MySQL is an excellent open-source relational database management system. In 2010, MySQL was forked into MariaDB after its purchase by Oracle. MariaDb is a community driven and developed, relational database management system (RDBMS). It continues to be a free and open-source software licensed under the GNU General Public License.

Clear RAM cache, buffer and swap space in Linux

 

Clean up the page-cache :

   # sync; echo 1> / proc / sys / vm / drop_caches

  

Clean dentrie and inode caches:

     # sync; echo 2> / proc / sys / vm / drop_caches

 

To clean the page-cache, dentrie and inode caches:

     # sync; echo 3> / proc / sys / vm / drop_caches

 

# swapoff -a && swapon -a

 

1) Shutdown the database and its services (pg_ctl stop , sudo service postgresql stop, etc.)

2) sync

3) sudo echo 3> / proc / sys / vm / drop_caches

4) Start the database server

 

free && sync && echo 3> / proc / sys / vm / drop_caches && free

Found this post interesting? Subscribe us  😊😉

Read more 

Configure Streaming Replication in PostgreSQL

All about Physical Replication and Log shipping in Postgres 

Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping

Oracle Account Issue

 Sometimes the database user account is getting locked if the login was tried with the wrong password. In 11g database, the user account will get locked after 10 consequent failed login attempts. Oracle 11g database's default profile makes the database account password lifetime as 3 months and the number of failed login attempts as 10 days.  Once these limits are reached, it will lock the account.

The following command can be used to change the default profile to allow an unlimited number of failed login attempt:

alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;

The following command can be used to change the default profile to allow an unlimited number of days as password lifetime.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

MySQL : Cache

 

Following caches are generally available in MySQL.

 

Streaming replication - Sync and Async, benefits of streaming replication over Log-shipping in Postgres

In my previous post here, I tried to explain how the log shipping works and its advantages. Let's see more about Streaming replication in Postgres.

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.

MySQL : Flush Tables

  “FLUSH TABLES” is generally used for closing all tables by force in MySQL. Its really interesting that how mysql do this. Most of time we are doing this because someone adds new tables outside of MySQL.

MySQL : Setup Master and Slave replica (Same server)

 

MySQL replication is a core process for maintaining multiple copies of data – and replication is a very important aspect in database administration.

This setup provides redundancy and fault tolerance such that in the event of a failure in the Master node, the databases can still be recovered in the Slave node.

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

pgtune | installation and usage

pgtune helps expands the database server to be as powerful as the hardware it's being deployed on , by taking an existing postgresql.conf file as an input, making changes to it based on the amount of RAM in your server and suggested workload, and output a new file.

pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds

-bash-4.1$ pg_basebackup -h oracleasm1.localdomain -D /u01/PG_Adventure
pg_basebackup: could not connect to server: FATAL:  number of requested standby connections exceeds … 

pg_basebackup : could not connect to server: FATAL: no pg_hba.conf entry for replication connection

pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off