" user. You are then connected to the Amazon Redshift cluster and can interactively enter commands. | DBA |
| Create an S3 bucket. | Open the Amazon S3 console, and create an S3 bucket to hold the exported files from Amazon Redshift. For help with this and other stories, see the "Related resources" section. | DBA |
| Create an IAM policy that supports copying data. | Open the IAM console and choose "Policies." Choose "Create policy," and choose the "JSON" tab. Copy and paste the IAM policy for copying data from the "Additional information" section. Important: Replace "s3\$1bucket\$1name" with your S3 bucket’s name. Choose "Review policy," enter a name and description for the policy. Choose "Create policy." | DBA |
| Create an IAM role to allow COPY operation for Amazon Redshift. | Open the IAM console and choose "Roles." Choose "Create role," and choose "AWS service" in "Select type of trusted entity." Choose "Redshift" for the service, choose "Redshift – Customizable," and then choose "Next." Choose the "Copy" policy you created earlier, and choose "Next." Enter a "Role name," and choose "Create role." | DBA |
| Associate IAM role with the Amazon Redshift cluster. | Open the Amazon Redshift console, and choose "Manage IAM roles." Choose "Available roles" from the dropdown menu and choose the role you created earlier. Choose "Apply changes." When the "Status" for the IAM role on the "Manage IAM roles" shows as "In-sync", you can run the "COPY" command. | DBA |
### Verify source data and object information before beginning the migration
| Task | Description | Skills required |
| --- | --- | --- |
| Verify the rows in the source Amazon Redshift tables. | Use the scripts in the "Additional information" section to verify and record the number of rows in the source Amazon Redshift tables. Remember to split the data evenly for the UNLOAD and COPY scripts. This will improve the data unloading and loading efficiency, because the data quantity covered by each script will be balanced. | DBA |
| Verify the number of database objects in the source Amazon Redshift cluster. | Use the scripts in the "Additional information" section to verify and record the number of databases, users, schemas, tables, views, and user-defined functions (UDFs) in your source Amazon Redshift cluster. | DBA |
| Verify SQL statement results before migration. | Some SQL statements for data validation should be sorted according to actual business and data situations. This is to verify the imported data to ensure it is consistent and displayed correctly. | DBA |
### Migrate data and objects to the target Region
| Task | Description | Skills required |
| --- | --- | --- |
| Generate Amazon Redshift DDL scripts. | Generate Data Definition Language (DDL) scripts by using the links from the "SQL statements to query Amazon Redshift" section in the "Additional information" section. These DDL scripts should include the "create user," "create schema," "privileges on schema to user," "create table/view," "privileges on objects to user," and "create function" queries. | DBA |
| Create objects in the Amazon Redshift cluster for the target Region. | Run the DDL scripts by using the AWS Command Line Interface (AWS CLI) in the AWS Region in China. These scripts will create objects in the Amazon Redshift cluster for the target Region. | DBA |
| Unload source Amazon Redshift cluster data to the S3 bucket. | Run the UNLOAD command to unload data from the Amazon Redshift cluster in the source Region to the S3 bucket. | DBA, Developer |
| Transfer source Region S3 bucket data to target Region S3 bucket. | Transfer the data from your source Region S3 bucket to the target S3 bucket. Because the "\$1 aws s3 sync" command cannot be used, make sure you use the process outlined in the "Transferring Amazon S3 data from AWS Regions to AWS Regions in China" article in the "Related resources" section. | Developer |
| Load data into the target Amazon Redshift cluster. | In the psql tool for your target Region, run the COPY command to load data from the S3 bucket to the target Amazon Redshift cluster. | DBA |
### Verify the data in the source and target Regions after the migration
| Task | Description | Skills required |
| --- | --- | --- |
| Verify and compare the number of rows in the source and target tables. | Verify and compare the number of table rows in the source and target Regions to ensure all are migrated. | DBA |
| Verify and compare the number of source and target database objects. | Verify and compare all database objects in the source and target Regions to ensure all are migrated. | DBA |
| Verify and compare SQL script results in the source and target Regions. | Run the SQL scripts prepared before the migration. Verify and compare the data to ensure that the SQL results are correct. | DBA |
| Reset the passwords of all users in the target Amazon Redshift cluster. | After the migration is complete and all data is verified, you should reset all user passwords for the Amazon Redshift cluster in the AWS Region in China. | DBA |
## Related resources
+ [Transferring Amazon S3 data from AWS Regions to AWS Regions in China](https://aws.amazon.com/cn/blogs/storage/transferring-amazon-s3-data-from-aws-regions-to-aws-regions-in-china/)
+ [Creating an S3 bucket](https://docs.aws.amazon.com/AmazonS3/latest/user-guide/create-bucket.html)
+ [Resetting an Amazon Redshift user password](https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_USER.html)
+ [psql documentation](https://www.postgresql.org/docs/8.4/static/app-psql.html)
## Additional information
*IAM policy for unloading data*
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:ListBucket"],
"Resource": ["arn:aws:s3:::s3_bucket_name"]
},
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:DeleteObject"],
"Resource": ["arn:aws:s3:::s3_bucket_name/*"]
}
]
}
```
*IAM policy for copying data*
```
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:ListBucket"],
"Resource": ["arn:aws:s3:::s3_bucket_name"]
},
{
"Effect": "Allow",
"Action": ["s3:GetObject"],
"Resource": ["arn:aws:s3:::s3_bucket_name/*"]
}
]
}
```
*SQL statements to query Amazon Redshift*
```
##Database
select * from pg_database where datdba>1;
##User
select * from pg_user where usesysid>1;
##Schema
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
##Table
select count(*) from pg_tables where schemaname not in ('pg_catalog','information_schema');
select schemaname,count(*) from pg_tables where schemaname not in ('pg_catalog','information_schema') group by schemaname order by 1;
##View
SELECT
n.nspname AS schemaname,c.relname AS viewname,pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM
pg_catalog.pg_class AS c
INNER JOIN
pg_catalog.pg_namespace AS n
ON c.relnamespace = n.oid
WHERE relkind = 'v' and n.nspname not in ('information_schema','pg_catalog');
##UDF
SELECT
n.nspname AS schemaname,
p.proname AS proname,
pg_catalog.pg_get_userbyid(p.proowner) as "Owner"
FROM pg_proc p
LEFT JOIN pg_namespace n on n.oid = p.pronamespace
WHERE p.proowner != 1;
```
*SQL scripts to generate DDL statements*
+ [Get\$1schema\$1priv\$1by\$1user script](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_get_schema_priv_by_user.sql)
+ [Generate\$1tbl\$1ddl script](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql)
+ [Generate\$1view\$1ddl](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_view_ddl.sql)
+ [Generate\$1user\$1grant\$1revoke\$1ddl](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_user_grant_revoke_ddl.sql)
+ [Generate\$1udf\$1ddl](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_udf_ddl.sql)
# Transport PostgreSQL databases between two Amazon RDS DB instances using pg\$1transport
*Raunak Rishabh and Jitender Kumar, Amazon Web Services*
## Summary
This pattern describes the steps for migrating extremely large databases between two Amazon Relational Database Service (Amazon RDS) for PostgreSQL DB instances by using the **pg\$1transport** extension. This extension provides a physical transport mechanism to move each database. By streaming the database files with minimal processing, it provides an extremely fast method for migrating large databases between DB instances with minimal downtime. This extension uses a pull model where the target DB instance imports the database from the source DB instance.
## Prerequisites and limitations
**Prerequisites **
+ Both DB instances must run the same major version of PostgreSQL.
+ The database must not exist on the target. Otherwise, the transport fails.
+ No extension other than **pg\$1transport** must be enabled in the source database.
+ All source database objects must be in the default **pg\$1default** tablespace.
+ The security group of the source DB instance should allow traffic from the target DB instance.
+ Install a PostgreSQL client like [psql](https://www.postgresql.org/docs/11/app-psql.html) or [PgAdmin](https://www.pgadmin.org/) to work with the Amazon RDS PostgreSQL DB instance. You can install the client either in your local system or use an Amazon Elastic Compute Cloud (Amazon EC2) instance. In this pattern, we use psql on an EC2 instance.
**Limitations **
+ You can't transport databases between different major versions of Amazon RDS for PostgreSQL.
+ The access privileges and ownership from the source database are not transferred to the target database.
+ You can't transport databases on read replicas or on parent instances of read replicas.
+ You can't use **reg** data types in any database tables that you plan to transport with this method.
+ You can run up to 32 total transports (including both imports and exports) at the same time on a DB instance.
+ You cannot rename or include/exclude tables. Everything is migrated as is.
**Caution**
+ Make backups before removing the extension, because removing the extension also removes dependent objects and some data that's critical to the operation of the database.
+ Consider the instance class and processes running on other databases on the source instance when you determine the number of workers and `work_mem` values for **pg\$1transport**.
+ When the transport starts, all connections on the source database are ended and the database is put into read-only mode.
**Note**
When the transport is running on one database, it doesn’t affect other databases on the same server.** **
**Product versions**
+ Amazon RDS for PostgreSQL 10.10 and later, and Amazon RDS for PostgreSQL 11.5 and later. For the latest version information, see [Transporting PostgreSQL Databases Between DB Instances](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.TransportableDB.html) in the Amazon RDS documentation.
## Architecture
![\[Transporting PostgreSQL databases between Amazon RDS DB instances\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/d5fb7ea3-32b7-4602-b382-3cf5c075c7c9/images/aec4d8d2-37a8-4136-9042-f9667ac4aebb.png)
## Tools
+ **pg\$1transport** provides a physical transport mechanism to move each database. By streaming the database files with minimal processing, physical transport moves data much faster than traditional dump and load processes and requires minimal downtime. PostgreSQL transportable databases use a pull model where the destination DB instance imports the database from the source DB instance. You install this extension on your DB instances when you prepare the source and target environments, as explained in this pattern.
+ [psql](https://www.postgresql.org/docs/11/app-psql.html) enables you to connect to, and work with, your PostgreSQL DB instances. To install **psql** on your system, see the [PostgreSQL Downloads](https://www.postgresql.org/download/) page.
## Epics
### Create the target parameter group
| Task | Description | Skills required |
| --- | --- | --- |
| Create a parameter group for the target system. | Specify a group name that identifies it as a target parameter group; for example, `pgtarget-param-group`. For instructions, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithDBInstanceParamGroups.html#USER_WorkingWithParamGroups.Creating). | DBA |
| Modify the parameters for the parameter group. | Set the following parameters:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/transport-postgresql-databases-between-two-amazon-rds-db-instances-using-pg-transport.html)For more information about these parameters, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.TransportableDB.html). | DBA |
### Create the source parameter group
| Task | Description | Skills required |
| --- | --- | --- |
| Create a parameter group for the source system. | Specify a group name that identifies it as a source parameter group; for example, `pgsource-param-group`. For instructions, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithDBInstanceParamGroups.html#USER_WorkingWithParamGroups.Creating). | DBA |
| Modify the parameters for the parameter group. | Set the following parameters:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/transport-postgresql-databases-between-two-amazon-rds-db-instances-using-pg-transport.html)For more information about these parameters, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.TransportableDB.html). | DBA |
### Prepare the target environment
| Task | Description | Skills required |
| --- | --- | --- |
| Create a new Amazon RDS for PostgreSQL DB instance to transport your source database to. | Determine the instance class and PostgreSQL version based on your business requirements. | DBA, Systems administrator, Database architect |
| Modify the security group of the target to allow connections on the DB instance port from the EC2 instance. | By default, the port for the PostgreSQL instance is 5432. If you're using another port, connections to that port must be open for the EC2 instance. | DBA, Systems administrator |
| Modify the instance, and assign the new target parameter group. | For example, `pgtarget-param-group`. | DBA |
| Restart the target Amazon RDS DB instance. | The parameters `shared_preload_libraries` and `max_worker_processes` are static parameters and require a reboot of the instance. | DBA, Systems administrator |
| Connect to the database from the EC2 instance using psql. | Use the command: psql -h -p PORT -U username -d database -W
| DBA |
| Create the pg\$1transport extension. | Run the following query as a user with the `rds_superuser` role:create extension pg_transport;
| DBA |
### Prepare the source environment
| Task | Description | Skills required |
| --- | --- | --- |
| Modify the security group of the source to allow connections on the DB instance port from the Amazon EC2 instance and target DB instance | By default, the port for PostgreSQL instance is 5432. If you're using another port, connections to that port must be open for the EC2 instance. | DBA, Systems administrator |
| Modify the instance and assign the new source parameter group. | For example, `pgsource-param-group`. | DBA |
| Restart the source Amazon RDS DB instance. | The parameters `shared_preload_libraries` and `max_worker_processes` are static parameters and require a reboot of the instance. | DBA |
| Connect to the database from the EC2 instance using psql. | Use the command: psql -h -p PORT -U username -d database -W
| DBA |
| Create the pg\$1transport extension and remove all other extensions from the databases to be transported. | The transport will fail if there are any extensions other than **pg\$1transport** installed on the source database. This command must by run by a user with the `rds_superuser` role. | DBA |
### Perform the transport
| Task | Description | Skills required |
| --- | --- | --- |
| Perform a dry run. | Use the `transport.import_from_server` function to perform a dry run first:SELECT transport.import_from_server( 'source-db-instance-endpoint', source-db-instance-port, 'source-db-instance-user', 'source-user-password', 'source-database-name', 'destination-user-password', 'true');
The last parameter of this function (set to `true`) defines the dry run. This function displays any errors that you would see when you run the main transport. Resolve the errors before you run the main transport. | DBA |
| If the dry run is successful, initiate the database transport. | Run the `transport.import_from_server` function to perform the transport. It connects to the source and imports the data. SELECT transport.import_from_server( 'source-db-instance-endpoint', source-db-instance-port, 'source-db-instance-user', 'source-user-password', 'source-database-name', 'destination-user-password', false);
The last parameter of this function (set to `false`) indicates that this isn’t a dry run. | DBA |
| Perform post-transport steps. | After the database transport is complete:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/transport-postgresql-databases-between-two-amazon-rds-db-instances-using-pg-transport.html) | DBA |
## Related resources
+ [Amazon RDS documentation](https://docs.aws.amazon.com/rds/)
+ [pg\$1transport documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#PostgreSQL.TransportableDB.Setup)
+ [Migrating databases using RDS PostgreSQL Transportable Databases](https://aws.amazon.com/blogs/database/migrating-databases-using-rds-postgresql-transportable-databases/) (blog post)
+ [PostgreSQL downloads](https://www.postgresql.org/download/linux/redhat/)
+ [psql utility](https://www.postgresql.org/docs/11/app-psql.html)
+ [Creating a DB Parameter Group](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Creating)
+ [Modify Parameters in a DB Parameter Group](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Modifying)
+ [PostgreSQL downloads](https://www.postgresql.org/download/)