# Importing data into PostgreSQL on Amazon RDS
Suppose that you have an existing PostgreSQL deployment that you want to move to Amazon RDS. The complexity of your task depends on the size of your database and the types of database objects that you're transferring. For example, consider a database that contains datasets on the order of gigabytes, along with stored procedures and triggers. Such a database is going to be more complicated than a simple database with only a few megabytes of test data and no triggers or stored procedures.
We recommend that you use native PostgreSQL database migration tools under the following conditions:
+ You have a homogeneous migration, where you are migrating from a database with the same database engine as the target database.
+ You are migrating an entire database.
+ The native tools allow you to migrate your system with minimal downtime.
In most other cases, performing a database migration using AWS Database Migration Service (AWS DMS) is the best approach. AWS DMS can migrate databases without downtime and, for many database engines, continue ongoing replication until you are ready to switch over to the target database. You can migrate to either the same database engine or a different database engine using AWS DMS. If you are migrating to a different database engine than your source database, you can use the AWS Schema Conversion Tool (AWS SCT). You use AWS SCT to migrate schema objects that are not migrated by AWS DMS. For more information about AWS DMS, see [ What is AWS Database Migration Service?](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html)
Modify your DB parameter group to include the following settings *for your import only*. You should test the parameter settings to find the most efficient settings for your DB instance size. You also need to revert back to production values for these parameters after your import completes.
Modify your DB instance settings to the following:
+ Disable DB instance backups (set backup\$1retention to 0).
+ Disable Multi-AZ.
Modify your DB parameter group to include the following settings. You should only use these settings when importing data. You should test the parameter settings to find the most efficient settings for your DB instance size. You also need to revert back to production values for these parameters after your import completes.
| Parameter | Recommended value when importing | Description |
| --- | --- | --- |
| `maintenance_work_mem` | 524288, 1048576, 2097152, or 4194304 (in KB). These settings are comparable to 512 MB, 1 GB, 2 GB, and 4 GB. | The value for this setting depends on the size of your host. This parameter is used during CREATE INDEX statements and each parallel command can use this much memory. Calculate the best value so that you don't set this value so high that you run out of memory. |
| `max_wal_size` | 256 (for version 9.6), 4096 (for versions 10 and higher) | Maximum size to let the WAL grow during automatic checkpoints. Increasing this parameter can increase the amount of time needed for crash recovery. This parameter replaces `checkpoint_segments` for PostgreSQL 9.6 and later. For PostgreSQL version 9.6, this value is in 16 MB units. For later versions, the value is in 1 MB units. For example, in version 9.6, 128 means 128 chunks that are each 16 MB in size. In version 12.4, 2048 means 2048 chunks that are each 1 MB in size. |
| `checkpoint_timeout` | 1800 | The value for this setting allows for less frequent WAL rotation. |
| `synchronous_commit` | Off | Disable this setting to speed up writes. Turning this parameter off can increase the risk of data loss in the event of a server crash (do not turn off FSYNC). |
| `wal_buffers` | 8192 | This is value is in 8 KB units. This again helps your WAL generation speed |
| `autovacuum` | 0 | Disable the PostgreSQL auto vacuum parameter while you are loading data so that it doesn't use resources |
Use the `pg_dump -Fc` (compressed) or `pg_restore -j` (parallel) commands with these settings.
**Note**
The PostgreSQL command `pg_dumpall` requires super\$1user permissions that are not granted when you create a DB instance, so it cannot be used for importing data.
**Topics**
+ [
# Importing a PostgreSQL database from an Amazon EC2 instance
](PostgreSQL.Procedural.Importing.EC2.md)
+ [
# Using the \$1copy command to import data to a table on a PostgreSQL DB instance
](PostgreSQL.Procedural.Importing.Copy.md)
+ [
# Importing data from Amazon S3 into an RDS for PostgreSQL DB instance
](USER_PostgreSQL.S3Import.md)
+ [
# Transporting PostgreSQL databases between DB instances
](PostgreSQL.TransportableDB.md)
# Importing a PostgreSQL database from an Amazon EC2 instance
If you have data in a PostgreSQL server on an Amazon EC2 instance and want to move it to a PostgreSQL DB instance, you can follow this process to migrate the data.
1. Create a file using pg\$1dump that contains the data to be loaded
1. Create the target DB instance
1. Use *psql* to create the database on the DB instance and load the data
1. Create a DB snapshot of the DB instance
The following sections provide more details on each step listed above.
## Step 1: Create a file using pg\$1dump that contains the data to load
The `pg_dump` utility uses the COPY command to create a schema and data dump of a PostgreSQL database. The dump script generated by `pg_dump` loads data into a database with the same name and recreates the tables, indexes, and foreign keys. You can use the `pg_restore` command and the `-d` parameter to restore the data to a database with a different name.
Before you create the data dump, you should query the tables to be dumped to get a row count so you can confirm the count on the target DB instance.
The following command creates a dump file called mydb2dump.sql for a database called mydb2.
```
prompt>pg_dump dbname=mydb2 -f mydb2dump.sql
```
## Step 2: Create the target DB instance
Create the target PostgreSQL DB instance using either the Amazon RDS console, AWS CLI, or API. Create the instance with the backup retention setting set to 0 and disable Multi-AZ. Doing so allows faster data import. You must create a database on the instance before you can dump the data. The database can have the same name as the database that is contained the dumped data. Alternatively, you can create a database with a different name. In this case, you use the `pg_restore` command and the `-d` parameter to restore the data into the newly named database.
For example, the following commands can be used to dump, restore, and rename a database.
```
pg_dump -Fc -v -h [endpoint of instance] -U [master username] [database] > [database].dump
createdb [new database name]
pg_restore -v -h [endpoint of instance] -U [master username] -d [new database name] [database].dump
```
## Step 3: Use psql to create the database on the DB instance and load data
You can use the same connection you used to run the pg\$1dump command to connect to the target DB instance and recreate the database. Using *psql*, you can use the master user name and master password to create the database on the DB instance
The following example uses *psql* and a dump file named mydb2dump.sql to create a database called mydb2 on a PostgreSQL DB instance called mypginstance:
For Linux, macOS, or Unix:
```
psql \
-f mydb2dump.sql \
--host mypginstance.555555555555.aws-region.rds.amazonaws.com \
--port 8199 \
--username myawsuser \
--password password \
--dbname mydb2
```
For Windows:
```
psql ^
-f mydb2dump.sql ^
--host mypginstance.555555555555.aws-region.rds.amazonaws.com ^
--port 8199 ^
--username myawsuser ^
--password password ^
--dbname mydb2
```
**Note**
Specify a password other than the prompt shown here as a security best practice.
## Step 4: Create a DB snapshot of the DB instance
Once you have verified that the data was loaded into your DB instance, we recommend that you create a DB snapshot of the target PostgreSQL DB instance. DB snapshots are complete backups of your DB instance that can be used to restore your DB instance to a known state. A DB snapshot taken immediately after the load protects you from having to load the data again in case of a mishap. You can also use such a snapshot to seed new DB instances. For information about creating a DB snapshot, see [Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS](USER_CreateSnapshot.md).
# Using the \$1copy command to import data to a table on a PostgreSQL DB instance
The PostgreSQL `\copy` command is a meta-command available from the `psql` interactive client tool. You can use `\copy` to import data into a table on your RDS for PostgreSQL DB instance. To use the `\copy` command, you need to first create the table structure on the target DB instance so that `\copy` has a destination for the data being copied.
You can use `\copy` to load data from a comma-separated values (CSV) file, such as one that's been exported and saved to your client workstation.
To import the CSV data to the target RDS for PostgreSQL DB instance, first connect to the target DB instance using `psql`.
```
psql --host=db-instance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=target-db
```
You then run `\copy` command with the following parameters to identify the target for the data and its format.
+ `target_table` – The name of the table that should receive the data being copied from the CSV file.
+ `column_list` – Column specifications for the table.
+ `'filename'` – The complete path to the CSV file on your local workstation.
```
\copy target_table from '/path/to/local/filename.csv' WITH DELIMITER ',' CSV;
```
If your CSV file has column heading information, you can use this version of the command and parameters.
```
\copy target_table (column-1, column-2, column-3, ...)
from '/path/to/local/filename.csv' WITH DELIMITER ',' CSV HEADER;
```
If the `\copy` command fails, PostgreSQL outputs error messages.
Creating a new DB instance in the Database Preview environment using `psql` command with the `\copy` meta-command as shown in the following examples. This example uses *source-table* as the source table name, *source-table.csv* as the .csv file, and *target-db* as the target database:
For Linux, macOS, or Unix:
```
$psql target-db \
-U \
-p \
-h \
-c "\copy source-table from 'source-table.csv' with DELIMITER ','"
```
For Windows:
```
$psql target-db ^
-U ^
-p ^
-h ^
-c "\copy source-table from 'source-table.csv' with DELIMITER ','"
```
For complete details about the `\copy` command, see the [psql](http://www.postgresql.org/docs/current/static/app-psql.html) page in the PostgreSQL documentation, in the *Meta-Commands* section.
# Importing data from Amazon S3 into an RDS for PostgreSQL DB instance
You can import data that's been stored using Amazon Simple Storage Service into a table on an RDS for PostgreSQL DB instance. To do this, you first install the RDS for PostgreSQL `aws_s3` extension. This extension provides the functions that you use to import data from an Amazon S3 bucket. A *bucket* is an Amazon S3 container for objects and files. The data can be in a comma-separate value (CSV) file, a text file, or a compressed (gzip) file. Following, you can learn how to install the extension and how to import data from Amazon S3 into a table.
Your database must be running PostgreSQL version 10.7 or higher to import from Amazon S3 into RDS for PostgreSQL.
If you don't have data stored on Amazon S3, you need to first create a bucket and store the data. For more information, see the following topics in the *Amazon Simple Storage Service User Guide*.
+ [Create a bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/GetStartedWithS3.html#creating-bucket)
+ [Add an object to a bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/GetStartedWithS3.html#uploading-an-object-bucket)
Cross-account import from Amazon S3 is supported. For more information, see [ Granting cross-account permissions](https://docs.aws.amazon.com/AmazonS3/latest/userguide/example-walkthroughs-managing-access-example2.html) in the *Amazon Simple Storage Service User Guide*.
You can use the customer managed key for encryption while importing data from S3. For more information, see [ KMS keys stored in AWS KMS](https://docs.aws.amazon.com/AmazonS3/latest/userguide/UsingKMSEncryption.html) in the *Amazon Simple Storage Service User Guide*.
**Topics**
+ [
# Installing the aws\$1s3 extension
](USER_PostgreSQL.S3Import.InstallExtension.md)
+ [
# Overview of importing data from Amazon S3 data
](USER_PostgreSQL.S3Import.Overview.md)
+ [
# Setting up access to an Amazon S3 bucket
](USER_PostgreSQL.S3Import.AccessPermission.md)
+ [
# Importing data from Amazon S3 to your RDS for PostgreSQL DB instance
](USER_PostgreSQL.S3Import.FileFormats.md)
+ [
# Function reference
](USER_PostgreSQL.S3Import.Reference.md)
# Installing the aws\$1s3 extension
Before you can use Amazon S3 with your RDS for PostgreSQL DB instance, you need to install the `aws_s3` extension. This extension provides functions for importing data from an Amazon S3. It also provides functions for exporting data from an RDS for PostgreSQL DB instance to an Amazon S3 bucket. For more information, see [Exporting data from an RDS for PostgreSQL DB instance to Amazon S3](postgresql-s3-export.md). The `aws_s3` extension depends on some of the helper functions in the `aws_commons` extension, which is installed automatically when needed.
**To install the `aws_s3` extension**
1. Use psql (or pgAdmin) to connect to the RDS for PostgreSQL DB instance as a user that has `rds_superuser` privileges. If you kept the default name during the setup process, you connect as `postgres`.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. To install the extension, run the following command.
```
postgres=> CREATE EXTENSION aws_s3 CASCADE;
NOTICE: installing required extension "aws_commons"
CREATE EXTENSION
```
1. To verify that the extension is installed, you can use the psql `\dx` metacommand.
```
postgres=> \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+---------------------------------------------
aws_commons | 1.2 | public | Common data types across AWS services
aws_s3 | 1.1 | public | AWS S3 extension for importing data from S3
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
```
The functions for importing data from Amazon S3 and exporting data to Amazon S3 are now available to use.
# Overview of importing data from Amazon S3 data
**To import S3 data into Amazon RDS**
First, gather the details that you need to supply to the function. These include the name of the table on your RDS for PostgreSQL DB instance, and the bucket name, file path, file type, and AWS Region where the Amazon S3 data is stored. For more information, see [View an object](https://docs.aws.amazon.com/AmazonS3/latest/userguide/OpeningAnObject.html) in the *Amazon Simple Storage Service User Guide*.
**Note**
Multi part data import from Amazon S3 isn't currently supported.
1. Get the name of the table into which the `aws_s3.table_import_from_s3` function is to import the data. As an example, the following command creates a table `t1` that can be used in later steps.
```
postgres=> CREATE TABLE t1
(col1 varchar(80),
col2 varchar(80),
col3 varchar(80));
```
1. Get the details about the Amazon S3 bucket and the data to import. To do this, open the Amazon S3 console at [https://console.aws.amazon.com/s3/](https://console.aws.amazon.com/s3/), and choose **Buckets**. Find the bucket containing your data in the list. Choose the bucket, open its Object overview page, and then choose Properties.
Make a note of the bucket name, path, the AWS Region, and file type. You need the Amazon Resource Name (ARN) later, to set up access to Amazon S3 through an IAM role. For more more information, see [Setting up access to an Amazon S3 bucket](USER_PostgreSQL.S3Import.AccessPermission.md). The image following shows an example.
![\[Image of a file object in an Amazon S3 bucket.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/aws_s3_import-export_s3_bucket-info.png)
1. You can verify the path to the data on the Amazon S3 bucket by using the AWS CLI command `aws s3 cp`. If the information is correct, this command downloads a copy of the Amazon S3 file.
```
aws s3 cp s3://amzn-s3-demo-bucket/sample_file_path ./
```
1. Set up permissions on your RDS for PostgreSQL DB instance to allow access to the file on the Amazon S3 bucket. To do so, you use either an AWS Identity and Access Management (IAM) role or security credentials. For more information, see [Setting up access to an Amazon S3 bucket](USER_PostgreSQL.S3Import.AccessPermission.md).
1. Supply the path and other Amazon S3 object details gathered (see step 2) to the `create_s3_uri` function to construct an Amazon S3 URI object. To learn more about this function, see [aws\$1commons.create\$1s3\$1uri](USER_PostgreSQL.S3Import.Reference.md#USER_PostgreSQL.S3Import.create_s3_uri). The following is an example of constructing this object during a psql session.
```
postgres=> SELECT aws_commons.create_s3_uri(
'docs-lab-store-for-rpg',
'versions_and_jdks_listing.csv',
'us-west-1'
) AS s3_uri \gset
```
In the next step, you pass this object (`aws_commons._s3_uri_1`) to the `aws_s3.table_import_from_s3` function to import the data to the table.
1. Invoke the `aws_s3.table_import_from_s3` function to import the data from Amazon S3 into your table. For reference information, see [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3). For examples, see [Importing data from Amazon S3 to your RDS for PostgreSQL DB instance](USER_PostgreSQL.S3Import.FileFormats.md).
# Setting up access to an Amazon S3 bucket
To import data from an Amazon S3 file, give the RDS for PostgreSQL DB instance permission to access the Amazon S3 bucket containing the file. You provide access to an Amazon S3 bucket in one of two ways, as described in the following topics.
**Topics**
+ [
## Using an IAM role to access an Amazon S3 bucket
](#USER_PostgreSQL.S3Import.ARNRole)
+ [
## Using security credentials to access an Amazon S3 bucket
](#USER_PostgreSQL.S3Import.Credentials)
+ [
## Troubleshooting access to Amazon S3
](#USER_PostgreSQL.S3Import.troubleshooting)
## Using an IAM role to access an Amazon S3 bucket
Before you load data from an Amazon S3 file, give your RDS for PostgreSQL DB instance permission to access the Amazon S3 bucket the file is in. This way, you don't have to manage additional credential information or provide it in the [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3) function call.
To do this, create an IAM policy that provides access to the Amazon S3 bucket. Create an IAM role and attach the policy to the role. Then assign the IAM role to your DB instance.
**To give an RDS for PostgreSQL DB instance access to Amazon S3 through an IAM role**
1. Create an IAM policy.
This policy provides the bucket and object permissions that allow your RDS for PostgreSQL DB instance to access Amazon S3.
Include in the policy the following required actions to allow the transfer of files from an Amazon S3 bucket to Amazon RDS:
+ `s3:GetObject`
+ `s3:ListBucket`
Include in the policy the following resources to identify the Amazon S3 bucket and objects in the bucket. This shows the Amazon Resource Name (ARN) format for accessing Amazon S3.
+ arn:aws:s3:::*amzn-s3-demo-bucket*
+ arn:aws:s3:::*amzn-s3-demo-bucket*/\$1
For more information on creating an IAM policy for RDS for PostgreSQL, see [Creating and using an IAM policy for IAM database access](UsingWithRDS.IAMDBAuth.IAMPolicy.md). See also [Tutorial: Create and attach your first customer managed policy](https://docs.aws.amazon.com/IAM/latest/UserGuide/tutorial_managed-policies.html) in the *IAM User Guide*.
The following AWS CLI command creates an IAM policy named `rds-s3-import-policy` with these options. It grants access to a bucket named *amzn-s3-demo-bucket*.
**Note**
Make a note of the Amazon Resource Name (ARN) of the policy returned by this command. You need the ARN in a subsequent step when you attach the policy to an IAM role.
**Example**
For Linux, macOS, or Unix:
```
aws iam create-policy \
--policy-name rds-s3-import-policy \
--policy-document '{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "s3import",
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Effect": "Allow",
"Resource": [
"arn:aws:s3:::amzn-s3-demo-bucket",
"arn:aws:s3:::amzn-s3-demo-bucket/*"
]
}
]
}'
```
For Windows:
```
aws iam create-policy ^
--policy-name rds-s3-import-policy ^
--policy-document '{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "s3import",
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Effect": "Allow",
"Resource": [
"arn:aws:s3:::amzn-s3-demo-bucket",
"arn:aws:s3:::amzn-s3-demo-bucket/*"
]
}
]
}'
```
1. Create an IAM role.
You do this so Amazon RDS can assume this IAM role to access your Amazon S3 buckets. For more information, see [Creating a role to delegate permissions to an IAM user](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-user.html) in the *IAM User Guide*.
We recommend using the `[aws:SourceArn](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourcearn)` and `[aws:SourceAccount](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_condition-keys.html#condition-keys-sourceaccount)` global condition context keys in resource-based policies to limit the service's permissions to a specific resource. This is the most effective way to protect against the [confused deputy problem](https://docs.aws.amazon.com/IAM/latest/UserGuide/confused-deputy.html).
If you use both global condition context keys and the `aws:SourceArn` value contains the account ID, the `aws:SourceAccount` value and the account in the `aws:SourceArn` value must use the same account ID when used in the same policy statement.
+ Use `aws:SourceArn` if you want cross-service access for a single resource.
+ Use `aws:SourceAccount` if you want to allow any resource in that account to be associated with the cross-service use.
In the policy, be sure to use the `aws:SourceArn` global condition context key with the full ARN of the resource. The following example shows how to do so using the AWS CLI command to create a role named `rds-s3-import-role`.
**Example**
For Linux, macOS, or Unix:
```
aws iam create-role \
--role-name rds-s3-import-role \
--assume-role-policy-document '{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"aws:SourceAccount": "111122223333",
"aws:SourceArn": "arn:aws:rds:us-east-1:111122223333:db:dbname"
}
}
}
]
}'
```
For Windows:
```
aws iam create-role ^
--role-name rds-s3-import-role ^
--assume-role-policy-document '{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"aws:SourceAccount": "111122223333",
"aws:SourceArn": "arn:aws:rds:us-east-1:111122223333:db:dbname"
}
}
}
]
}'
```
1. Attach the IAM policy that you created to the IAM role that you created.
The following AWS CLI command attaches the policy created in the previous step to the role named `rds-s3-import-role` Replace `your-policy-arn` with the policy ARN that you noted in an earlier step.
**Example**
For Linux, macOS, or Unix:
```
aws iam attach-role-policy \
--policy-arn your-policy-arn \
--role-name rds-s3-import-role
```
For Windows:
```
aws iam attach-role-policy ^
--policy-arn your-policy-arn ^
--role-name rds-s3-import-role
```
1. Add the IAM role to the DB instance.
You do so by using the AWS Management Console or AWS CLI, as described following.
### Console
**To add an IAM role for a PostgreSQL DB instance using the console**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Choose the PostgreSQL DB instance name to display its details.
1. On the **Connectivity & security** tab, in the **Manage IAM roles **section, choose the role to add under **Add IAM roles to this instance **.
1. Under **Feature**, choose **s3Import**.
1. Choose **Add role**.
### AWS CLI
**To add an IAM role for a PostgreSQL DB instance using the CLI**
+ Use the following command to add the role to the PostgreSQL DB instance named `my-db-instance`. Replace *`your-role-arn`* with the role ARN that you noted in a previous step. Use `s3Import` for the value of the `--feature-name` option.
**Example**
For Linux, macOS, or Unix:
```
aws rds add-role-to-db-instance \
--db-instance-identifier my-db-instance \
--feature-name s3Import \
--role-arn your-role-arn \
--region your-region
```
For Windows:
```
aws rds add-role-to-db-instance ^
--db-instance-identifier my-db-instance ^
--feature-name s3Import ^
--role-arn your-role-arn ^
--region your-region
```
### RDS API
To add an IAM role for a PostgreSQL DB instance using the Amazon RDS API, call the [ AddRoleToDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_AddRoleToDBInstance.html) operation.
## Using security credentials to access an Amazon S3 bucket
If you prefer, you can use security credentials to provide access to an Amazon S3 bucket instead of providing access with an IAM role. You do so by specifying the `credentials` parameter in the [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3) function call.
The `credentials` parameter is a structure of type `aws_commons._aws_credentials_1`, which contains AWS credentials. Use the [aws\$1commons.create\$1aws\$1credentials](USER_PostgreSQL.S3Import.Reference.md#USER_PostgreSQL.S3Import.create_aws_credentials) function to set the access key and secret key in an `aws_commons._aws_credentials_1` structure, as shown following.
```
postgres=> SELECT aws_commons.create_aws_credentials(
'sample_access_key', 'sample_secret_key', '')
AS creds \gset
```
After creating the `aws_commons._aws_credentials_1 `structure, use the [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3) function with the `credentials` parameter to import the data, as shown following.
```
postgres=> SELECT aws_s3.table_import_from_s3(
't', '', '(format csv)',
:'s3_uri',
:'creds'
);
```
Or you can include the [aws\$1commons.create\$1aws\$1credentials](USER_PostgreSQL.S3Import.Reference.md#USER_PostgreSQL.S3Import.create_aws_credentials) function call inline within the `aws_s3.table_import_from_s3` function call.
```
postgres=> SELECT aws_s3.table_import_from_s3(
't', '', '(format csv)',
:'s3_uri',
aws_commons.create_aws_credentials('sample_access_key', 'sample_secret_key', '')
);
```
## Troubleshooting access to Amazon S3
If you encounter connection problems when attempting to import data from Amazon S3, see the following for recommendations:
+ [Troubleshooting Amazon RDS identity and access](security_iam_troubleshoot.md)
+ [Troubleshooting Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/troubleshooting.html) in the *Amazon Simple Storage Service User Guide*
+ [Troubleshooting Amazon S3 and IAM](https://docs.aws.amazon.com/IAM/latest/UserGuide/troubleshoot_iam-s3.html) in the *IAM User Guide*
# Importing data from Amazon S3 to your RDS for PostgreSQL DB instance
You import data from your Amazon S3 bucket by using the `table_import_from_s3` function of the aws\$1s3 extension. For reference information, see [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3).
**Note**
The following examples use the IAM role method to allow access to the Amazon S3 bucket. Thus, the `aws_s3.table_import_from_s3` function calls don't include credential parameters.
The following shows a typical example.
```
postgres=> SELECT aws_s3.table_import_from_s3(
't1',
'',
'(format csv)',
:'s3_uri'
);
```
The parameters are the following:
+ `t1` – The name for the table in the PostgreSQL DB instance to copy the data into.
+ `''` – An optional list of columns in the database table. You can use this parameter to indicate which columns of the S3 data go in which table columns. If no columns are specified, all the columns are copied to the table. For an example of using a column list, see [Importing an Amazon S3 file that uses a custom delimiter](#USER_PostgreSQL.S3Import.FileFormats.CustomDelimiter).
+ `(format csv)` – PostgreSQL COPY arguments. The copy process uses the arguments and format of the [PostgreSQL COPY](https://www.postgresql.org/docs/current/sql-copy.html) command to import the data. Choices for format include comma-separated value (CSV) as shown in this example, text, and binary. The default is text.
+ `s3_uri` – A structure that contains the information identifying the Amazon S3 file. For an example of using the [aws\$1commons.create\$1s3\$1uri](USER_PostgreSQL.S3Import.Reference.md#USER_PostgreSQL.S3Import.create_s3_uri) function to create an `s3_uri` structure, see [Overview of importing data from Amazon S3 data](USER_PostgreSQL.S3Import.Overview.md).
For more information about this function, see [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3).
The `aws_s3.table_import_from_s3` function returns text. To specify other kinds of files for import from an Amazon S3 bucket, see one of the following examples.
**Note**
Importing 0 bytes file will cause an error.
**Topics**
+ [
## Importing an Amazon S3 file that uses a custom delimiter
](#USER_PostgreSQL.S3Import.FileFormats.CustomDelimiter)
+ [
## Importing an Amazon S3 compressed (gzip) file
](#USER_PostgreSQL.S3Import.FileFormats.gzip)
+ [
## Importing an encoded Amazon S3 file
](#USER_PostgreSQL.S3Import.FileFormats.Encoded)
## Importing an Amazon S3 file that uses a custom delimiter
The following example shows how to import a file that uses a custom delimiter. It also shows how to control where to put the data in the database table using the `column_list` parameter of the [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3) function.
For this example, assume that the following information is organized into pipe-delimited columns in the Amazon S3 file.
```
1|foo1|bar1|elephant1
2|foo2|bar2|elephant2
3|foo3|bar3|elephant3
4|foo4|bar4|elephant4
...
```
**To import a file that uses a custom delimiter**
1. Create a table in the database for the imported data.
```
postgres=> CREATE TABLE test (a text, b text, c text, d text, e text);
```
1. Use the following form of the [aws\$1s3.table\$1import\$1from\$1s3](USER_PostgreSQL.S3Import.Reference.md#aws_s3.table_import_from_s3) function to import data from the Amazon S3 file.
You can include the [aws\$1commons.create\$1s3\$1uri](USER_PostgreSQL.S3Import.Reference.md#USER_PostgreSQL.S3Import.create_s3_uri) function call inline within the `aws_s3.table_import_from_s3` function call to specify the file.
```
postgres=> SELECT aws_s3.table_import_from_s3(
'test',
'a,b,d,e',
'DELIMITER ''|''',
aws_commons.create_s3_uri('amzn-s3-demo-bucket', 'pipeDelimitedSampleFile', 'us-east-2')
);
```
The data is now in the table in the following columns.
```
postgres=> SELECT * FROM test;
a | b | c | d | e
---+------+---+---+------+-----------
1 | foo1 | | bar1 | elephant1
2 | foo2 | | bar2 | elephant2
3 | foo3 | | bar3 | elephant3
4 | foo4 | | bar4 | elephant4
```
## Importing an Amazon S3 compressed (gzip) file
The following example shows how to import a file from Amazon S3 that is compressed with gzip. The file that you import needs to have the following Amazon S3 metadata:
+ Key: `Content-Encoding`
+ Value: `gzip`
If you upload the file using the AWS Management Console, the metadata is typically applied by the system. For information about uploading files to Amazon S3 using the AWS Management Console, the AWS CLI, or the API, see [Uploading objects](https://docs.aws.amazon.com/AmazonS3/latest/userguide/upload-objects.html) in the *Amazon Simple Storage Service User Guide*.
For more information about Amazon S3 metadata and details about system-provided metadata, see [Editing object metadata in the Amazon S3 console](https://docs.aws.amazon.com/AmazonS3/latest/userguide/add-object-metadata.html) in the *Amazon Simple Storage Service User Guide*.
Import the gzip file into your RDS for PostgreSQL DB instance as shown following.
```
postgres=> CREATE TABLE test_gzip(id int, a text, b text, c text, d text);
postgres=> SELECT aws_s3.table_import_from_s3(
'test_gzip', '', '(format csv)',
'amzn-s3-demo-bucket', 'test-data.gz', 'us-east-2'
);
```
## Importing an encoded Amazon S3 file
The following example shows how to import a file from Amazon S3 that has Windows-1252 encoding.
```
postgres=> SELECT aws_s3.table_import_from_s3(
'test_table', '', 'encoding ''WIN1252''',
aws_commons.create_s3_uri('amzn-s3-demo-bucket', 'SampleFile', 'us-east-2')
);
```
# Function reference
**Topics**
+ [
## aws\$1s3.table\$1import\$1from\$1s3
](#aws_s3.table_import_from_s3)
+ [
## aws\$1commons.create\$1s3\$1uri
](#USER_PostgreSQL.S3Import.create_s3_uri)
+ [
## aws\$1commons.create\$1aws\$1credentials
](#USER_PostgreSQL.S3Import.create_aws_credentials)
## aws\$1s3.table\$1import\$1from\$1s3
Imports Amazon S3 data into an Amazon RDS table. The `aws_s3` extension provides the `aws_s3.table_import_from_s3` function. The return value is text.
### Syntax
The required parameters are `table_name`, `column_list` and `options`. These identify the database table and specify how the data is copied into the table.
You can also use the following parameters:
+ The `s3_info` parameter specifies the Amazon S3 file to import. When you use this parameter, access to Amazon S3 is provided by an IAM role for the PostgreSQL DB instance.
```
aws_s3.table_import_from_s3 (
table_name text,
column_list text,
options text,
s3_info aws_commons._s3_uri_1
)
```
+ The `credentials` parameter specifies the credentials to access Amazon S3. When you use this parameter, you don't use an IAM role.
```
aws_s3.table_import_from_s3 (
table_name text,
column_list text,
options text,
s3_info aws_commons._s3_uri_1,
credentials aws_commons._aws_credentials_1
)
```
### Parameters
*table\$1name*
A required text string containing the name of the PostgreSQL database table to import the data into.
*column\$1list*
A required text string containing an optional list of the PostgreSQL database table columns in which to copy the data. If the string is empty, all columns of the table are used. For an example, see [Importing an Amazon S3 file that uses a custom delimiter](USER_PostgreSQL.S3Import.FileFormats.md#USER_PostgreSQL.S3Import.FileFormats.CustomDelimiter).
*options*
A required text string containing arguments for the PostgreSQL `COPY` command. These arguments specify how the data is to be copied into the PostgreSQL table. For more details, see the [PostgreSQL COPY documentation](https://www.postgresql.org/docs/current/sql-copy.html).
*s3\$1info*
An `aws_commons._s3_uri_1` composite type containing the following information about the S3 object:
+ `bucket` – The name of the Amazon S3 bucket containing the file.
+ `file_path` – The Amazon S3 file name including the path of the file.
+ `region` – The AWS Region that the file is in. For a listing of AWS Region names and associated values, see [Regions, Availability Zones, and Local Zones](Concepts.RegionsAndAvailabilityZones.md).
*credentials*
An `aws_commons._aws_credentials_1` composite type containing the following credentials to use for the import operation:
+ Access key
+ Secret key
+ Session token
For information about creating an `aws_commons._aws_credentials_1` composite structure, see [aws\$1commons.create\$1aws\$1credentials](#USER_PostgreSQL.S3Import.create_aws_credentials).
### Alternate syntax
To help with testing, you can use an expanded set of parameters instead of the `s3_info` and `credentials` parameters. Following are additional syntax variations for the `aws_s3.table_import_from_s3` function:
+ Instead of using the `s3_info` parameter to identify an Amazon S3 file, use the combination of the `bucket`, `file_path`, and `region` parameters. With this form of the function, access to Amazon S3 is provided by an IAM role on the PostgreSQL DB instance.
```
aws_s3.table_import_from_s3 (
table_name text,
column_list text,
options text,
bucket text,
file_path text,
region text
)
```
+ Instead of using the `credentials` parameter to specify Amazon S3 access, use the combination of the `access_key`, `session_key`, and `session_token` parameters.
```
aws_s3.table_import_from_s3 (
table_name text,
column_list text,
options text,
bucket text,
file_path text,
region text,
access_key text,
secret_key text,
session_token text
)
```
### Alternate parameters
*bucket*
A text string containing the name of the Amazon S3 bucket that contains the file.
*file\$1path*
A text string containing the Amazon S3 file name including the path of the file.
*region*
A text string identifying the AWS Region location of the file. For a listing of AWS Region names and associated values, see [Regions, Availability Zones, and Local Zones](Concepts.RegionsAndAvailabilityZones.md).
*access\$1key*
A text string containing the access key to use for the import operation. The default is NULL.
*secret\$1key*
A text string containing the secret key to use for the import operation. The default is NULL.
*session\$1token*
(Optional) A text string containing the session key to use for the import operation. The default is NULL.
## aws\$1commons.create\$1s3\$1uri
Creates an `aws_commons._s3_uri_1` structure to hold Amazon S3 file information. Use the results of the `aws_commons.create_s3_uri` function in the `s3_info` parameter of the [aws\$1s3.table\$1import\$1from\$1s3](#aws_s3.table_import_from_s3) function.
### Syntax
```
aws_commons.create_s3_uri(
bucket text,
file_path text,
region text
)
```
### Parameters
*bucket*
A required text string containing the Amazon S3 bucket name for the file.
*file\$1path*
A required text string containing the Amazon S3 file name including the path of the file.
*region*
A required text string containing the AWS Region that the file is in. For a listing of AWS Region names and associated values, see [Regions, Availability Zones, and Local Zones](Concepts.RegionsAndAvailabilityZones.md).
## aws\$1commons.create\$1aws\$1credentials
Sets an access key and secret key in an `aws_commons._aws_credentials_1` structure. Use the results of the `aws_commons.create_aws_credentials` function in the `credentials` parameter of the [aws\$1s3.table\$1import\$1from\$1s3](#aws_s3.table_import_from_s3) function.
### Syntax
```
aws_commons.create_aws_credentials(
access_key text,
secret_key text,
session_token text
)
```
### Parameters
*access\$1key*
A required text string containing the access key to use for importing an Amazon S3 file. The default is NULL.
*secret\$1key*
A required text string containing the secret key to use for importing an Amazon S3 file. The default is NULL.
*session\$1token*
An optional text string containing the session token to use for importing an Amazon S3 file. The default is NULL. If you provide an optional `session_token`, you can use temporary credentials.
# Transporting PostgreSQL databases between DB instances
By using PostgreSQL transportable databases for Amazon RDS, you can move a PostgreSQL database between two DB instances. This is a very fast way to migrate large databases between different DB instances. To use this approach, your DB instances must both run the same major version of PostgreSQL.
This capability requires that you install the `pg_transport` extension on both the source and the destination DB instance. The `pg_transport` extension provides a physical transport mechanism that moves the database files with minimal processing. This mechanism moves data much faster than traditional dump and load processes, with less downtime.
**Note**
PostgreSQL transportable databases are available in RDS for PostgreSQL 11.5 and higher, and RDS for PostgreSQL version 10.10 and higher.
To transport a PostgreSQL DB instance from one RDS for PostgreSQL DB instance to another, you first set up the source and destination instances as detailed in [ Setting up a DB instance for transport](PostgreSQL.TransportableDB.Setup.md). You can then transport the database by using the function described in [ Transporting a PostgreSQL database](PostgreSQL.TransportableDB.Transporting.md).
**Topics**
+ [
## What happens during database transport
](#PostgreSQL.TransportableDB.DuringTransport)
+ [
## Limitations for using PostgreSQL transportable databases
](#PostgreSQL.TransportableDB.Limits)
+ [
# Setting up to transport a PostgreSQL database
](PostgreSQL.TransportableDB.Setup.md)
+ [
# Transporting a PostgreSQL database to the destination from the source
](PostgreSQL.TransportableDB.Transporting.md)
+ [
# Transportable databases function reference
](PostgreSQL.TransportableDB.transport.import_from_server.md)
+ [
# Transportable databases parameter reference
](PostgreSQL.TransportableDB.Parameters.md)
## What happens during database transport
The PostgreSQL transportable databases feature uses a pull model to import the database from the source DB instance to the destination. The `transport.import_from_server` function creates the in-transit database on the destination DB instance. The in-transit database is inaccessible on the destination DB instance for the duration of the transport.
When transport begins, all current sessions on the source database are ended. Any databases other than the source database on the source DB instance aren't affected by the transport.
The source database is put into a special read-only mode. While it's in this mode, you can connect to the source database and run read-only queries. However, write-enabled queries and some other types of commands are blocked. Only the specific source database that is being transported is affected by these restrictions.
During transport, you can't restore the destination DB instance to a point in time. This is because the transport isn't transactional and doesn't use the PostgreSQL write-ahead log to record changes. If the destination DB instance has automatic backups enabled, a backup is automatically taken after transport completes. Point-in-time restores are available for times *after* the backup finishes.
If the transport fails, the `pg_transport` extension attempts to undo all changes to the source and destination DB instances. This includes removing the destination's partially transported database. Depending on the type of failure, the source database might continue to reject write-enabled queries. If this happens, use the following command to allow write-enabled queries.
```
ALTER DATABASE db-name SET default_transaction_read_only = false;
```
## Limitations for using PostgreSQL transportable databases
Transportable databases have the following limitations:
+ **Read replicas ** – You can't use transportable databases on read replicas or parent instances of read replicas.
+ **Unsupported column types** – You can't use the `reg` data types in any database tables that you plan to transport with this method. These types depend on system catalog object IDs (OIDs), which often change during transport.
+ **Tablespaces** – All source database objects must be in the default `pg_default` tablespace.
+ **Compatibility** – Both the source and destination DB instances must run the same major version of PostgreSQL.
+ **Extensions** – The source DB instance can have only the `pg_transport` installed.
+ **Roles and ACLs** – The source database's access privileges and ownership information aren't carried over to the destination database. All database objects are created and owned by the local destination user of the transport.
+ **Concurrent transports** – A single DB instance can support up to 32 concurrent transports, including both imports and exports, if worker processes have been configured properly.
+ **RDS for PostgreSQL DB instances only** – PostgreSQL transportable databases are supported on RDS for PostgreSQL DB instances only. You can't use it with on-premises databases or databases running on Amazon EC2.
# Setting up to transport a PostgreSQL database
Before you begin, make sure that your RDS for PostgreSQL DB instances meet the following requirements:
+ The RDS for PostgreSQL DB instances for source and destination must run the same version of PostgreSQL.
+ The destination DB can't have a database of the same name as the source DB that you want to transport.
+ The account you use to run the transport needs `rds_superuser` privileges on both the source DB and the destination DB.
+ The security group for the source DB instance must allow inbound access from the destination DB instance. This might already be the case if your source and destination DB instances are located in the VPC. For more information about security groups, see [Controlling access with security groups](Overview.RDSSecurityGroups.md).
Transporting databases from a source DB instance to a destination DB instance requires several changes to the DB parameter group associated with each instance. That means that you must create a custom DB parameter group for the source DB instance and create a custom DB parameter group for the destination DB instance.
**Note**
If your DB instances are already configured using custom DB parameter groups, you can start with step 2 in the following procedure.
**To configure the custom DB group parameters for transporting databases**
For the following steps, use an account that has `rds_superuser` privileges.
1. If the source and destination DB instances use a default DB parameter group, you need to create a custom DB parameter group using the appropriate version for your instances. You do this so you can change values for several parameters. For more information, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
1. In the custom DB parameter group, change values for the following parameters:
+ `shared_preload_libraries` – Add `pg_transport` to the list of libraries.
+ `pg_transport.num_workers` – The default value is 3. Increase or reduce this value as needed for your database. For a 200 GB database, we recommend no larger than 8. Keep in mind that if you increase the default value for this parameter, you should also increase the value of `max_worker_processes`.
+ `pg_transport.work_mem` – The default value is either 128 MB or 256 MB, depending on the PostgreSQL version. The default setting can typically be left unchanged.
+ `max_worker_processes` – The value of this parameter needs to be set using the following calculation:
```
(3 * pg_transport.num_workers) + 9
```
This value is required on the destination to handle various background worker processes involved in the transport. To learn more about `max_worker_processes,` see [Resource Consumption](https://www.postgresql.org/docs/current/runtime-config-resource.html) in the PostgreSQL documentation.
For more information about `pg_transport` parameters, see [Transportable databases parameter reference](PostgreSQL.TransportableDB.Parameters.md).
1. Reboot the source RDS for PostgreSQL DB instance and the destination instance so that the settings for the parameters take effect.
1. Connect to your RDS for PostgreSQL source DB instance.
```
psql --host=source-instance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
1. Remove extraneous extensions from the public schema of the DB instance. Only the `pg_transport` extension is allowed during the actual transport operation.
1. Install the `pg_transport` extension as follows:
```
postgres=> CREATE EXTENSION pg_transport;
CREATE EXTENSION
```
1. Connect to your RDS for PostgreSQL destination DB instance. Remove any extraneous extensions, and then install the `pg_transport` extension.
```
postgres=> CREATE EXTENSION pg_transport;
CREATE EXTENSION
```
# Transporting a PostgreSQL database to the destination from the source
After you complete the process described in [Setting up to transport a PostgreSQL database](PostgreSQL.TransportableDB.Setup.md), you can start the transport. To do so, run the `transport.import_from_server` function on the destination DB instance. In the syntax following you can find the function parameters.
```
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 `false` value shown in the example tells the function that this is not a dry run. To test your transport setup, you can specify `true` for the `dry_run` option when you call the function, as shown following:
```
postgres=> SELECT transport.import_from_server(
'docs-lab-source-db.666666666666aws-region.rds.amazonaws.com', 5432,
'postgres', '********', 'labdb', '******', true);
INFO: Starting dry-run of import of database "labdb".
INFO: Created connections to remote database (took 0.03 seconds).
INFO: Checked remote cluster compatibility (took 0.05 seconds).
INFO: Dry-run complete (took 0.08 seconds total).
import_from_server
--------------------
(1 row)
```
The INFO lines are output because the `pg_transport.timing` parameter is set to its default value, `true`. Set the `dry_run` to `false` when you run the command and the source database is imported to the destination, as shown following:
```
INFO: Starting import of database "labdb".
INFO: Created connections to remote database (took 0.02 seconds).
INFO: Marked remote database as read only (took 0.13 seconds).
INFO: Checked remote cluster compatibility (took 0.03 seconds).
INFO: Signaled creation of PITR blackout window (took 2.01 seconds).
INFO: Applied remote database schema pre-data (took 0.50 seconds).
INFO: Created connections to local cluster (took 0.01 seconds).
INFO: Locked down destination database (took 0.00 seconds).
INFO: Completed transfer of database files (took 0.24 seconds).
INFO: Completed clean up (took 1.02 seconds).
INFO: Physical transport complete (took 3.97 seconds total).
import_from_server
--------------------
(1 row)
```
This function requires that you provide database user passwords. Thus, we recommend that you change the passwords of the user roles you used after transport is complete. Or, you can use SQL bind variables to create temporary user roles. Use these temporary roles for the transport and then discard the roles afterwards.
If your transport isn't successful, you might see an error message similar to the following:
```
pg_transport.num_workers=8 25% of files transported failed to download file data
```
The "failed to download file data" error message indicates that the number of worker processes isn't set correctly for the size of the database. You might need to increase or decrease the value set for `pg_transport.num_workers`. Each failure reports the percentage of completion, so you can see the impact of your changes. For example, changing the setting from 8 to 4 in one case resulted in the following:
```
pg_transport.num_workers=4 75% of files transported failed to download file data
```
Keep in mind that the `max_worker_processes` parameter is also taken into account during the transport process. In other words, you might need to modify both `pg_transport.num_workers` and `max_worker_processes` to successfully transport the database. The example shown finally worked when the `pg_transport.num_workers` was set to 2:
```
pg_transport.num_workers=2 100% of files transported
```
For more information about the `transport.import_from_server` function and its parameters, see [Transportable databases function reference](PostgreSQL.TransportableDB.transport.import_from_server.md).
# Transportable databases function reference
The `transport.import_from_server` function transports a PostgreSQL database by importing it from a source DB instance to a destination DB instance. It does this by using a physical database connection transport mechanism.
Before starting the transport, this function verifies that the source and the destination DB instances are the same version and are compatible for the migration. It also confirms that the destination DB instance has enough space for the source.
**Syntax**
```
transport.import_from_server(
host text,
port int,
username text,
password text,
database text,
local_password text,
dry_run bool
)
```
**Return Value**
None.
**Parameters**
You can find descriptions of the `transport.import_from_server` function parameters in the following table.
****
| Parameter | Description |
| --- | --- |
| host | The endpoint of the source DB instance. |
| port | An integer representing the port of the source DB instance. PostgreSQL DB instances often use port 5432. |
| username | The user of the source DB instance. This user must be a member of the `rds_superuser` role. |
| password | The user password of the source DB instance. |
| database | The name of the database in the source DB instance to transport. |
| local\$1password | The local password of the current user for the destination DB instance. This user must be a member of the `rds_superuser` role. |
| dry\$1run | An optional Boolean value specifying whether to perform a dry run. The default is `false`, which means the transport proceeds.To confirm compatibility between the source and destination DB instances without performing the actual transport, set dry\$1run to true. |
**Example**
For an example, see [Transporting a PostgreSQL database to the destination from the source](PostgreSQL.TransportableDB.Transporting.md).
# Transportable databases parameter reference
Several parameters control the behavior of the `pg_transport` extension. Following, you can find descriptions of these parameters.
**`pg_transport.num_workers`**
The number of workers to use for the transport process. The default is 3. Valid values are 1–32. Even the largest database transports typically require fewer than 8 workers. The value of this setting on the destination DB instance is used by both destination and source during transport.
**`pg_transport.timing` **
Specifies whether to report timing information during the transport. The default is `true`, meaning that timing information is reported. We recommend that you leave this parameter set to `true` so you can monitor progress. For example output, see [Transporting a PostgreSQL database to the destination from the source](PostgreSQL.TransportableDB.Transporting.md).
**`pg_transport.work_mem`**
The maximum amount of memory to allocate for each worker. The default is 131072 kilobytes (KB) or 262144 KB (256 MB), depending on the PostgreSQL version. The minimum value is 64 megabytes (65536 KB). Valid values are in kilobytes (KBs) as binary base-2 units, where 1 KB = 1024 bytes.
The transport might use less memory than is specified in this parameter. Even large database transports typically require less than 256 MB (262144 KB) of memory per worker.