Philip

How to set up Streaming Replication in Postgres

by

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

  1. 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.
  2. 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.
  3. Restart the master server for the changes to take effect.
  4. 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.
  5. In the recovery.conf file on the standby server, add the following lines to specify the connection details for the master server:

Read more

How does a WITH clause work in PostgreSQL?

by

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:

Read more

Vacuuming in PostgreSQL

by

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.

Read more

All about Indexing in PostgreSQL

by

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:

Read more

All about Table Partitioning in PostgreSQL

by

What is Partitioning in PostgreSQL

Partitioning in PostgreSQL is a technique for dividing a large table into smaller, more manageable pieces called partitions. Each partition contains a subset of the data in the table, and the data in each partition is organized and stored in a way that is optimized for the specific subset of data it contains.

Overall, partitioning is a useful technique for dividing large tables into smaller, more manageable pieces in PostgreSQL. It can improve the performance and manageability of the table, and can make it easier to work with large datasets in your database.

Read more

The Quick Redshift AWS Tutorial – Everything you need to get started.

by

AWS Redshift is a very powerful database, but it is quite different from traditional a RDBMS in terms of architecture and also the sorts of decisions that you need to make and planning that you need to perform before building and operating a Amazon Redshift cluster.

This Redshift AWS tutorial aims to give you everything that you need to get started with AWS Redshift in as little time as possible, by covering the basics of architecture and the structure of the database, optimizations that you can make and some advice on performance tuning and maintaining a AWS Redshift cluster.  

Read more

AWS Well-Architected Framework – The 5 Pillars

by

The AWS Well-Architected Framework is a set of Documents and Tools that sets out best practices and procedures to follow to build high quality applications in the cloud on AWS

The core of the well architected framework is 5 pillars that group the important considerations and practices that you need to stick to when building in AWS.

The 5 Pillars of the AWS Well-Architected Framework

  • Security
  • Reliability
  • Performance Efficiency
  • Operational Excellence
  • Cost Optimization

Read more

IAM Role in AWS

by

In AWS IAM Roles are similar to IAM Users in that they can have policies and permissions attached to them. However, they cannot be authorized (login) and they do not really have the policies etc attached to them. They appear to have them but underneath, those policies flow down the the actual users beneath.

The idea is that Roles will help you to group users together and also that they can help you in temporarily granting a level of access or permission to a user and give you an easy way to revoke that.

Read more

AWS IAM Notes – AWS Solutions Architect

by

This is some rough notes on IAM in AWS, covering IAM Roles, IAM Users and IAM Policies. The notes are aims at studying for the AWS Solutions Architect Certification and should cover the main points required for that exam.

AWS IAM can be summed up by saying that it authenticates and authorities you to and in AWS.

Root user is first user and has all permissions to everything on the account. It can’t be removed.

Read more