Storing MySQL Authentication Credentials mysql_config_editor:⭐️ we can create encrypted file that contain root credential , so root doesn’t have to fill password each time it connect to MySQL mysql_config_editor is one of MySQL executable programmes that configure, it configure the authentication information in a hidden file of the current user’s directory. the hidden file name…
concurrency (dead-block , live blocking )
here we are referring to lock between deterrent userswhether the need lock how long they hold the locks . locks : the tool is used by SQl server concunrancey is called locks and locks clash in form of blocks there two type of blocks:1.dead locks2. live locks.locks : is tool used by SQL server to put hold…
dbatools
DBATools is a collection PowerShell command over 700 command that can help DBA to achieve common task in a more automated manner in this section will demonstrate how to install dbatools , and how to use it deferent type of task that include 1- taking backup2- taking script of the job3-migirationand so on install DBATOOLS…
MySQL Storage Engines
Storage Engines MySQL supports a variety of table types to cater to different needs: To manage these diverse table types, MySQL uses different components known as storage engines. These engines are responsible for handling SQL operations for the tables. The storage engines include: MySQL employs a Pluggable Storage Engine architecture, which allows for the dynamic…
Performance Tuning with pg_profile and pgbench
PostgreSQL Performance Tuning Lab with pg_profile and pgbench Steps if you looking to get oracle AWR report in postgresql look no further than using pg_profile , its extension help you get full statistics report of postgresql instance that you view in your browser to check lots of statistics regarding I/O memory blocking and long running…
pgloader for migrating for postgresql
I had a scenario where the data team needed all tables from different databases to be combined into a single database for reporting and analysis using a tool like Metabase. Initially, I faced challenges trying to restore tables into a single database manually, which led to several issues. However, I discovered pgloader, a tool typically used…
automate patroni deployment using partoni autobase
Configuring Patroni can be challenging and time-consuming, but thanks to Autobase, the deployment process becomes much easier. Whether you’re deploying on-premises or in the cloud, such as Google Cloud or Amazon Web Services, Autobase simplifies the process. With Autobase’s console UI, you simply enter your server information and select the version you want, and then…
Health check using pgmetrics
One of the key tasks for any PostgreSQL DBA is conducting daily health checks to ensure the database remains healthy. Performing these checks is critical as it helps identify potential issues and resolve them before they impact production. PostgreSQL provides several views to monitor various metrics such as wait states, CPU usage, I/O, and more….
auditing in postgresql
Tracking any changes made at the database level is crucial for holding accountable any users with access to PostgreSQL. During my experience as a database administrator, I encountered two situations where applications reported data loss. Upon investigation, we discovered that a table had been truncated. Unfortunately, we couldn’t confirm the source of the issue because…
PostgreSql maintenance schedule
backup We will use pg_basebackup to take a complete backup of the PostgreSQL data directory. A separate directory will be created for storing backups, preferably on a separate mount point. The ownership of this directory will be assigned to the postgres user to ensure proper permissions. sudo mkdir -p /db_backup/base_backup sudo chown -R postgres:postgres db_backup/ Log in as the postgres user…