AWS - Restoring Postgres on EC2 instance from S3 backup

In this tutorial, we want to restore a Postgres on EC2 instance from a backup stored at S3.

Simple wget command will do it. On a newly created EC2 instance with Ubuntu 14.04:

$ https://s3.amazonaws.com/db-backup.gz
$ gunzip https://s3.amazonaws.com/db-backup.gz

We will see how to install Postgres on an Ubuntu 14.04 instance and go over some basic ways to use it.

We may want to update our local apt repository, and then get the Postgres package and a "contrib" package that adds some additional utilities and functionality:

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

By default, Postgres uses roles to aid in authentication and authorization. The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, we'll need to log into that account, and we can do that by typing the following command

ubuntu@ip-172-31-9-140:~$ sudo -i -u postgres
postgres@ip-172-31-9-140:~$

As we can see, it gives us a shell prompt for the postgres user.

We can get a Postgres prompt immediately by typing psql:

postgres@ip-172-31-9-140:~$ psql
psql (9.1.18)
Type "help" for help.

postgres=#

We will be auto-logged in and will be able to interact with the database right away.

We can easily get out of the PostgreSQL prompt by typing \q:

postgres=# \q
postgres@ip-172-31-9-140:~$

We used the pg_dumpall program to backup. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions. The resulting dump can be restored with psql:

postgres@ip-172-31-9-140:~$ psql -f /home/ubuntu/db-backup postgres

If the size of the backup is huge, we may want to use nohup like this:

postgres@ip-172-31-9-140:~$ nohup psql -f /home/ubuntu/db-backup postgres > out &

We can check the progress of restoring:

postgres@ip-172-31-9-140:~$ tail -f out1
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
...

We can list all dbs using \list:

ubuntu@ip-172-31-9-140:~$ sudo -i -u postgres
postgres@ip-172-31-9-140:~$ psql
psql (9.1.18)
Type "help" for help.

postgres=# \list
postgres=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 result    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =Tc/postgres         +
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
           |          |          |             |             | =c/postgres
 test0     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 test1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 test2     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

We can connect to a specific db using \c:

postgres=# \c result
You are now connected to database "result" as user "postgres".
result=#

To list a specific table, we can use \dt:

result=# \dt result
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+--------
 public | result | table | postgres
(1 row)

To list other commands, we can use \?:

result=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h [NAME]              help on syntax of SQL commands, * for all commands
  \q                     quit psql

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S]  [PATTERN]      list conversions
  \dC     [PATTERN]      list casts
  \dd[S]  [PATTERN]      show comments on objects
  \ddp    [PATTERN]      list default privileges
  \dD[S]  [PATTERN]      list domains
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[+]  [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[+]  [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dE[S+] [PATTERN]      list foreign tables
  \dx[+]  [PATTERN]      list extensions
  \l[+]                  list all databases
  \sf[+] FUNCNAME        show a function's definition
  \z      [PATTERN]      same as \dp

Formatting
  \a                     toggle between unaligned and aligned output mode
  ...

To see the first 10 elements:

result=# select * from result limit 10;

The number of rows in the result table is over 500 million, and it will most likely take long if we use normal count method:

result=# select count(*) from result;

So, we may want to take an alternative way of counting it. We can get an approximate count using the reltuples field from the pg_class catalog table:

result=# SELECT reltuples FROM pg_class WHERE relname = 'result';
 reltuples
------------
 5.8565e+08

AWS (Amazon Web Services)

1. AWS : EKS (Elastic Container Service for Kubernetes)
2. AWS : Creating a snapshot (cloning an image)
3. AWS : Attaching Amazon EBS volume to an instance
4. AWS : Adding swap space to an attached volume via mkswap and swapon
5. AWS : Creating an EC2 instance and attaching Amazon EBS volume to the instance using Python boto module with User data
6. AWS : Creating an instance to a new region by copying an AMI
7. AWS : S3 (Simple Storage Service) 1
8. AWS : S3 (Simple Storage Service) 2 - Creating and Deleting a Bucket
9. AWS : S3 (Simple Storage Service) 3 - Bucket Versioning
10. AWS : S3 (Simple Storage Service) 4 - Uploading a large file
11. AWS : S3 (Simple Storage Service) 5 - Uploading folders/files recursively
12. AWS : S3 (Simple Storage Service) 6 - Bucket Policy for File/Folder View/Download
13. AWS : S3 (Simple Storage Service) 7 - How to Copy or Move Objects from one region to another
14. AWS : S3 (Simple Storage Service) 8 - Archiving S3 Data to Glacier
15. AWS : Creating a CloudFront distribution with an Amazon S3 origin
16. AWS : Creating VPC with CloudFormation
17. AWS : WAF (Web Application Firewall) with preconfigured CloudFormation template and Web ACL for CloudFront distribution
18. AWS : CloudWatch & Logs with Lambda Function / S3
19. AWS : Lambda Serverless Computing with EC2, CloudWatch Alarm, SNS
20. AWS : Lambda and SNS - cross account
21. AWS : CLI (Command Line Interface)
22. AWS : CLI (ECS with ALB & autoscaling)
23. AWS : ECS with cloudformation and json task definition
24. AWS Application Load Balancer (ALB) and ECS with Flask app
25. AWS : Load Balancing with HAProxy (High Availability Proxy)
26. AWS : VirtualBox on EC2
27. AWS : NTP setup on EC2
28. AWS: jq with AWS
29. AWS & OpenSSL : Creating / Installing a Server SSL Certificate
30. AWS : OpenVPN Access Server 2 Install
31. AWS : VPC (Virtual Private Cloud) 1 - netmask, subnets, default gateway, and CIDR
32. AWS : VPC (Virtual Private Cloud) 2 - VPC Wizard
33. AWS : VPC (Virtual Private Cloud) 3 - VPC Wizard with NAT
34. DevOps / Sys Admin Q & A (VI) - AWS VPC setup (public/private subnets with NAT)
35. AWS - OpenVPN Protocols : PPTP, L2TP/IPsec, and OpenVPN
36. AWS : Autoscaling group (ASG)
37. AWS : Setting up Autoscaling Alarms and Notifications via CLI and Cloudformation
38. AWS : Adding a SSH User Account on Linux Instance
39. AWS : Windows Servers - Remote Desktop Connections using RDP
40. AWS : Scheduled stopping and starting an instance - python & cron
41. AWS : Detecting stopped instance and sending an alert email using Mandrill smtp
42. AWS : Elastic Beanstalk with NodeJS
43. AWS : Elastic Beanstalk Inplace/Rolling Blue/Green Deploy
44. AWS : Identity and Access Management (IAM) Roles for Amazon EC2
45. AWS : Identity and Access Management (IAM) Policies, sts AssumeRole, and delegate access across AWS accounts
46. AWS : Identity and Access Management (IAM) sts assume role via aws cli2
47. AWS : Creating IAM Roles and associating them with EC2 Instances in CloudFormation
48. AWS Identity and Access Management (IAM) Roles, SSO(Single Sign On), SAML(Security Assertion Markup Language), IdP(identity provider), STS(Security Token Service), and ADFS(Active Directory Federation Services)
49. AWS : Amazon Route 53
50. AWS : Amazon Route 53 - DNS (Domain Name Server) setup
51. AWS : Amazon Route 53 - subdomain setup and virtual host on Nginx
52. AWS Amazon Route 53 : Private Hosted Zone
53. AWS : SNS (Simple Notification Service) example with ELB and CloudWatch
54. AWS : Lambda with AWS CloudTrail
55. AWS : SQS (Simple Queue Service) with NodeJS and AWS SDK
56. AWS : Redshift data warehouse
57. AWS : CloudFormation
58. AWS : CloudFormation Bootstrap UserData/Metadata
59. AWS : CloudFormation - Creating an ASG with rolling update
60. AWS : Cloudformation Cross-stack reference
61. AWS : OpsWorks
62. AWS : Network Load Balancer (NLB) with Autoscaling group (ASG)
63. AWS CodeDeploy : Deploy an Application from GitHub
64. AWS EC2 Container Service (ECS)
65. AWS EC2 Container Service (ECS) II
66. AWS Hello World Lambda Function
67. AWS Lambda Function Q & A
68. AWS Node.js Lambda Function & API Gateway
69. AWS API Gateway endpoint invoking Lambda function
70. AWS API Gateway invoking Lambda function with Terraform
71. AWS API Gateway invoking Lambda function with Terraform - Lambda Container
72. Amazon Kinesis Streams
73. AWS: Kinesis Data Firehose with Lambda and ElasticSearch
74. Amazon DynamoDB
75. Amazon DynamoDB with Lambda and CloudWatch
76. Loading DynamoDB stream to AWS Elasticsearch service with Lambda
77. Amazon ML (Machine Learning)
78. Simple Systems Manager (SSM)
79. AWS : RDS Connecting to a DB Instance Running the SQL Server Database Engine
80. AWS : RDS Importing and Exporting SQL Server Data
81. AWS : RDS PostgreSQL & pgAdmin III
82. AWS : RDS PostgreSQL 2 - Creating/Deleting a Table
83. AWS : MySQL Replication : Master-slave
84. AWS : MySQL backup & restore
85. AWS RDS : Cross-Region Read Replicas for MySQL and Snapshots for PostgreSQL
86. AWS : Restoring Postgres on EC2 instance from S3 backup
87. AWS : Q & A
88. AWS : Security
89. AWS : Security groups vs. network ACLs
90. AWS : Scaling-Up
91. AWS : Networking
92. AWS : Single Sign-on (SSO) with Okta
93. AWS : JIT (Just-in-Time) with Okta