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.