AWS CLI - RDS Point in time recovery

Question

Hello, I want to use Point-in-time recovery for my Aurora Postgres Cluster. It turned out that this recovery brings back only cluster and not db instance.
doc: 
> If you use the AWS CLI to restore a DB cluster to a specified time, you must explicitly create the primary instance for your DB cluster. The primary instance is the first instance that is created in a DB cluster.

What happens with data stored if writer instance is not recovered? Does PITR make any sense?

Accepted Answer

The Aurora architecture is built on a design that completely splits compute and storage.  The Aurora Storage engine is a purpose-built distributed storage engine where we have offloaded certain aspects of database processing so that it understands the persistence and consistency of the relational model.  Consequently, there is nothing in the primary writer instance that affects the integrity of the cluster storage volume which allows for point-in-time restores to simply restore the cluster storage volume in a transactionally consistent state despite not having a compute instance running the database engine attached to it.

This separation of compute and storage as well as the fact that all compute instances in an Aurora cluster attach to the same cluster storage volume is also what enables a failover of the writer instance to another reader in the same region without any data-loss.  Contrast this with what one might see when using asynchronous replication technologies to keep a read replica with its' own independent storage in-sync with the primary writer.  Hope this helps...

Answer

Hi,

According to your question, I think you are curious about several things as below.

the structure of Aurora Database (Cluster and Instances)
PITR for Aurora PostgreSQL

Aurora DB is briefly organized as follows:

DB Cluster
- managing the instances
- DB Cluster Volume: Storage
Writer Instance (Primary Instance)
- (computing) Instances that directly enter and store data
Reader Instance (Replica Instance)
- (computing) Instances that only allow to read data

In other words, if a user wants to write or modify data in the DB and has the permission to do so, they connect to the Writer Instance. When you apply changes in the Writer Instance, the results are saved to DB Cluster Volume.

In summary, the writer instance corresponds to a computing instance that writes data, so you don't have to restore it, regardless of changes to the database.

[+] (Amazon Aurora DB clusters)[https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.html] (AWS Documentation)(ENG)

If you would like to restore a DB cluster to a specified time, you can use the AWS CLI command restore-db-cluster-to-point-in-time to create a new DB cluster.

aws rds restore-db-cluster-to-point-in-time \
    --source-db-cluster-identifier <mysourcedbcluster> \
    --db-cluster-identifier <mytargetdbcluster> \
    --restore-to-time <2017-10-14T23:45:00.000Z>

You also need to create the db instance (writer instance; primary instance) while using AWS CLI - create-db-instance. (You don't have to create separately if you use AWS Console)

aws rds create-db-instance \
    --db-instance-identifier <test-mysql-instance> \
    --db-instance-class <db.t3.micro> \
    --engine <postgresql> \
    --master-username <admin> \
    --master-user-password <password> \
    --allocated-storage <20>
    --db-cluster-identifier <mytargetdbcluster>

[+] Restoring a DB cluster to a specified time (AWS Documentation)(ENG)

If I have missed anything or answered wrong, please feel free to ask me again. Also you have any questions, comment please!

AWS CLI - RDS Point in time recovery

Relevant content