{"type":"doc","content":[{"type":"heading","attrs":{"level":2},"content":[{"text":"Problem Statement ","type":"text","marks":[{"type":"strong"}]},{"text":": To take a database dump from one host and restore it to another host. Both the hosts may or may not be in the same cluster environment. An example is to take the database dump from the PROD environment and restore it to the QA environment.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Database Dump Approaches","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Approach 1 :","type":"text","marks":[{"type":"strong"}]},{"text":" Connect to Pod, Take Dump and Copy to Local System","type":"text"}]},{"type":"paragraph","content":[{"text":"Prerequisites","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Cluster environment should be configured on the Local System.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Database Credentials","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Steps","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect to Cluster and then Pod.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"kubectl config use-context ","type":"text"}]},{"type":"paragraph","content":[{"text":"kubectl exec -it -- sh","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Take dump.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"pg_dump -Fp --no-acl --no-owner --no-privileges -h -U -W > backup.sql","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Copy dump to the local environment.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"kubectl cp pod_name:/backup.sql.gz local-destination_path -n playground","type":"text"}]},{"type":"paragraph","content":[{"text":"Challenges","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"The Pod may get evicted while taking the dump.","type":"text"}]},{"type":"paragraph","content":[{"text":"Reasons","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Since the dump is being taken after connecting to the pod, the pod should have enough space to store the dump.","type":"text"}]},{"type":"paragraph","content":[{"text":"If the pod is low on resources (as would be the case for a dump which is several GBs for example), it will simply get evicted.","type":"text"}]},{"type":"paragraph","content":[{"text":"Possible Solution","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Taking compressed dump is one option, as this will take less space.","type":"text"}]},{"type":"paragraph","content":[{"text":"pg_dump -Fp --no-acl --no-owner --no-privileges -h -U -W | gzip > backup.sql.gz","type":"text"}]},{"type":"paragraph","content":[{"text":"However, if the pod gets evicted in this case also, because of low resources, one should try other approaches as mentioned below.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Approach 2 :","type":"text","marks":[{"type":"strong"}]},{"text":" Connect to AWS RDS from Local System and Take Dump","type":"text"}]},{"type":"paragraph","content":[{"text":"Prerequisites","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"AWS CLI access","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connection to the DB Instance should be authorized","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Database Credentials","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Steps","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Connect to the AWS RDS using AWS CLI to take the dump on Local System","type":"text"}]},{"type":"paragraph","content":[{"text":"pg_dump -Fp --no-acl --no-owner --no-privileges -h -U -W > backup.sql","type":"text"}]},{"type":"paragraph","content":[{"text":"Challenges","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"The following error may be faced : Could not connect to server: Connection timed out","type":"text"}]},{"type":"paragraph","content":[{"text":"Reasons","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The Database Instance may not be publicly accessible.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connection from the device may not be authorized by the DB Instance’s Security Group.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Contact the Admin with Console Access to make this Database Instance publicily accessible /add your IP to the security group.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Approach 3 :","type":"text","marks":[{"type":"strong"}]},{"text":" Create/ Locate an existing DB Snapshot through AWS CLI","type":"text"}]},{"type":"paragraph","content":[{"text":"Prerequisites","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"AWS CLI access","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Steps","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A DB snapshot can be created using","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"aws rds create-db-snapshot --db-instance-identifier mydbinstance --db-snapshot-identifier","type":"text"}]},{"type":"paragraph","content":[{"text":"mydbsnapshot","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The DB snapshot may be existing from before and can be located through","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"aws rds describe-db-snapshots","type":"text"}]},{"type":"paragraph","content":[{"text":"If the DB instance is known, the search can be narrowed down using :","type":"text"}]},{"type":"paragraph","content":[{"text":"aws rds describe-db-snapshots --db-instance-identifier db-instance-name","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Approach 4 :","type":"text","marks":[{"type":"strong"}]},{"text":" Connect to Cluster, and Take Dump directly into Local System","type":"text"}]},{"type":"paragraph","content":[{"text":"Prerequisites","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Cluster environment should be configured on the Local System.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Database Credentials","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Steps","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect to Cluster.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"kubectl config use-context ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Take dump from Pod directly to Local using the following command :","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"kubectl exec -it -n playground -- sh -c \"export PGPASSWORD=\"\"; pg_dump -","type":"text"}]},{"type":"paragraph","content":[{"text":"h -U -d \" > .sql","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Dump Restore Approaches","type":"text"}]},{"type":"paragraph","content":[{"text":"If a .sql file is created on local system using one of the approaches (Approach 1,2 or 4), the dump restore can be done as follows","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Approach 1 : ","type":"text","marks":[{"type":"strong"}]},{"text":"Restore using pg_restore","type":"text"}]},{"type":"paragraph","content":[{"text":"Prerequisites","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Cluster environment should be configured on the Local System.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Database Credentials","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Postgres client version should be equal to or higher than the Postgres server version, for restore to work.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Steps","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Connect to Cluster, where the dump needs to be restored.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"kubectl config use-context ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Restore dump to database host directly from the local dump as follows","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"kubectl exec -it -n playground -- sh -c \"export PGPASSWORD=\"\"; pg_restore - h -U -d \" < .sql","type":"text"}]},{"type":"paragraph","content":[{"text":"Challenges","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"A connection error may be thrown if the destination environment is not accessible.","type":"text"}]},{"type":"paragraph","content":[{"text":"Possible Solution","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"The Admin with Console Access can be requested to make the environment accessible for sometime, till the dump restore is over. Once the dump restore is done, the environment can be moved to its previous VPC settings.","type":"text"}]},{"type":"paragraph","content":[{"text":"However, if a DB snapshot was created as part of Approach 3, the snapshot can be restored as follows","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Approach 2 :","type":"text","marks":[{"type":"underline"},{"type":"strong"}]},{"text":" Restore DB Snapshot through AWS CLI","type":"text"}]},{"type":"paragraph","content":[{"text":"Prerequisites","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"AWS CLI access","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Steps","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"The snapshot can be restored as follows :","type":"text"}]},{"type":"paragraph","content":[{"text":"aws rds restore-db-instance-from-db-snapshot --db-instance-identifier mynewdbinstance --db-","type":"text"}]},{"type":"paragraph","content":[{"text":"snapshot-identifier mydbsnapshot","type":"text"}]},{"type":"paragraph","content":[{"text":"Challenges","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"If a snapshot from one environment (says Prod) is to be restored to another environment (say QA), it must","type":"text"}]},{"type":"paragraph","content":[{"text":"be made available in the latter environment.","type":"text"}]},{"type":"paragraph","content":[{"text":"Possible Solution","type":"text","marks":[{"type":"underline"},{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Get the account ID of the destination environment using the following command :","type":"text"}]},{"type":"paragraph","content":[{"text":"aws sts get-caller-identity","type":"text"}]},{"type":"paragraph","content":[{"text":"The snapshot can then be added to the destination environment using","type":"text"}]},{"type":"paragraph","content":[{"text":"aws rds modify-db-snapshot-attribute --db-snapshot-identifier manual-snapshot --attribute-name","type":"text"}]},{"type":"paragraph","content":[{"text":"restore --values-to-add '[\"Account ID\"]'","type":"text"}]},{"type":"paragraph","content":[{"text":"For this to work, the snapshot should be a manual snapshot. If it is an automated snapshot, it can copied","type":"text"}]},{"type":"paragraph","content":[{"text":"as a manual snapshot as follows :","type":"text"}]},{"type":"paragraph","content":[{"text":"aws rds copy-db-snapshot --source-db-snapshot-identifier automated-snapshot-name --target-db-","type":"text"}]},{"type":"paragraph","content":[{"text":"snapshot-identifier mydbsnapshotcopy --copy-tags","type":"text"}]},{"type":"paragraph","content":[{"text":"If the command for modifying db snapshot attribute throws an Invalid Customer ID error, contact Admin","type":"text"}]},{"type":"paragraph","content":[{"text":"with Console Access to copy the snapshot from one environment to another.","type":"text"}]}],"version":1}