Databases Archives - Page 83 of 89

How do you identify high load tables/indexes in Postgres

11/12/2022 by Philip

To identify high-load tables or indexes in Postgres, you can use the pg_stat_all_tables or pg_stat_all_indexes views. These views provide information about the access statistics for each table and index in a database. To access these views, you can connect to your Postgres database using a tool like psql and then run a query like the …

How can I secure a Postgres Database

11/12/2022 by Philip

There are several steps you can take to secure a Postgres database. Here are a few:

Use a strong password for the database administrator account to prevent unauthorized access.
Enable SSL connections to encrypt data transmitted between the database server and client applications.
Configure your database server to listen on a private network interface or localhost, instead of all interfaces. This will prevent remote connections to the database server.
Use the built-in Postgres role-based access control (RBAC) system to grant specific users access to specific database objects and operations.
Regularly back up your database to protect against data loss and enable recovery in case of a disaster.
Use Postgres’ built-in auditing capabilities to track and log access to the database, so you can monitor for suspicious activity and quickly identify and respond to security threats.
Keep your Postgres installation and all database extensions up to date with the latest security patches to protect against known vulnerabilities.

When and How do you use pg_rewind() in PostgreSQL?

06/12/2022 by Philip

The pg_rewind utility in PostgreSQL is used to synchronize a standby server with a new master server in a scenario where the old master server has been promoted to a new master server and the standby server needs to catch up with the new master server’s state. This utility can be used when the old master server still has the transaction logs (WAL) that were generated after the promotion, and it can be used to “rewind” the state of the standby server to the point in time where the promotion occurred.

To use the pg_rewind utility, you need to perform the following steps:

How do I Re-Attach a Master once a Replica has been Promoted in Postgres

06/12/2022 by Philip

To reattach a master server once a replica has been promoted in PostgreSQL, you need to perform the following steps:

On the new master server, edit the postgresql.conf configuration file and set the wal_level parameter to hot_standby. This will enable the new master server to keep a sufficient amount of transaction log data (WAL) to allow the old master server to connect and apply the changes.
Restart the new master server for the changes to take effect.
On the old master server, edit the postgresql.conf configuration file and set the hot_standby parameter to on. This will enable the old master server to connect to the new master server and start receiving the changes.
In the recovery.conf file on the old master server, add the following lines to specify the connection details for the new master server:

PostgreSQL – How to Create Database

06/12/2022 by Philip

To create a new database in PostgreSQL, you can use the CREATE DATABASE statement. Here is the basic syntax for this statement: Replace database_name with the name of the database that you want to create. For example, to create a database named my_database, you would use the following statement: This will create a new database …

How do you promote a replica in Postgres?

06/12/2022 by Philip

To promote a replica in PostgreSQL, you need to perform the following steps:

On the standby server, shut down the PostgreSQL service using the appropriate command for your operating system. For example, on Linux you can use the systemctl command like this:

sudo systemctl stop postgresql

How to set up Streaming Replication in Postgres

06/12/2022 by Philip

To set up streaming replication in PostgreSQL, you need to perform the following steps:

Set up the master server and the standby server. The master server is the primary server that will receive all the write operations, while the standby server will receive a copy of all the changes made on the master server and will be used for failover in case the master server becomes unavailable.
On the master server, edit the postgresql.conf configuration file and set the wal_level parameter to hot_standby. This will enable the master server to keep a sufficient amount of transaction log data (WAL) to allow the standby server to connect and apply the changes.
Restart the master server for the changes to take effect.
On the standby server, edit the postgresql.conf configuration file and set the hot_standby parameter to on. This will enable the standby server to connect to the master server and start receiving the changes.
In the recovery.conf file on the standby server, add the following lines to specify the connection details for the master server:

How does a WITH clause work in PostgreSQL?

06/12/2022 by Philip

In PostgreSQL, the WITH clause allows you to define one or more subqueries that can be referenced by name in the main query. This is often called a common table expression (CTE) because it’s essentially a temporary result set that you can reference within your main query.

Here’s an example of how the WITH clause can be used:

Vacuuming in PostgreSQL

04/01/202406/12/2022 by Philip

Is Vacuuming important in Postgresql

PostgreSQL is a database management system, and as such, vacuuming is an important maintenance task to help keep the database running efficiently. Vacuuming helps to clean up dead tuples (rows that have been deleted or obsoleted) and free up space on the database, which can help to improve performance and prevent the database from becoming bloated or fragmented. It’s generally recommended to vacuum a PostgreSQL database regularly to help maintain its health and performance.

All about Indexing in PostgreSQL

06/12/202206/12/2022 by Philip

What are the types of indexes available in PostgresQL and when should each be used?

PostgreSQL offers several different types of indexes, each of which can be useful in different situations. Here are some of the most common types of indexes in PostgreSQL: