
Add credentials
- Create a new pipeline or open an existing pipeline.
- Expand the left side of your screen to view the file browser.
- Scroll down and click on a file named
io_config.yaml
. - Enter the following keys and values under the key named
default
(you can have multiple profiles, add it under whichever is relevant to you)
Pro-only fields
Only in Mage Pro.Try our fully managed solution to access this advanced feature.
POSTGRES_SSL_MODE
corresponds to thesslmode
parameter in psycopg2 (e.g.,require
,verify-full
).POSTGRES_SSL_ROOTCERT
,POSTGRES_SSL_CERT
, andPOSTGRES_SSL_KEY
should be absolute paths to your SSL files on disk.
SSH tunneling
If you want to connect to Postgres cluster with ssh tunnel, update the value ofPOSTGRES_CONNECTION_METHOD
to ssh_tunnel
, and enter the values for keys with prefix POSTGRES_SSH
.
Key | Description | Sample value |
---|---|---|
POSTGRES_SSH_HOST | The host of the intermediate bastion server. | 123.45.67.89 |
POSTGRES_SSH_PORT | The port of the intermediate bastion server. Default value: 22 | 22 |
POSTGRES_SSH_USERNAME | The username used to connect to the bastion server. | username |
POSTGRES_SSH_PASSWORD | (Optional) The password used to connect to the bastion server. It should be set if you authenticate with the bastion server with password. | password |
POSTGRES_SSH_PKEY | (Optional) The path to the private key used to connect to the bastion server. It should be set if you authenticate with the bastion server with private key. | /path/to/private/key |
Using SQL block
- Create a new pipeline or open an existing pipeline.
- Add a data loader, transformer, or data exporter block.
- Select
SQL
. - Under the
Data provider
dropdown, selectPostgreSQL
. - Under the
Profile
dropdown, selectdefault
(or the profile you added credentials underneath). - Next to the
Save to schema
label, enter the schema name you want this block to save data to. - Under the
Write policy
dropdown, selectReplace
orAppend
(please see SQL blocks guide for more information on write policies). - Enter in this test query:
SELECT 1
. - Run the block.
Using Python block
- Create a new pipeline or open an existing pipeline.
- Add a data loader, transformer, or data exporter block (the code snippet below is for a data loader).
- Select
Generic (no template)
. - Enter this code snippet (note: change the
config_profile
fromdefault
if you have a different profile):
- Run the block.
Export a dataframe
Here is an example code snippet to export a dataframe to Postgres:- Custom types
overwrite_types
dict in data exporter config
Here is an example code snippet:
Method arguments
Field name | Description | Example values |
---|---|---|
allow_reserved_words | Whether to allow using reserved words as column names. | True/False (default: False) |
auto_clean_name | Whether to automatically clean the column name (replace the empty space with underscore, avoid using number as the prefix of the column name) | True/False (default: True) |
case_sensitive | Whether to support case sensitive columns | True/False (default: False) |
cascade_on_drop | Whether to cascade the table drop. | True/False (default: False) |
drop_table_on_replace | Whether to drop the table when “if_exists” param is set to “replace”. | True/False (default: False) |
if_exists | Specify resolution policy if table name already exists | ”fail”/“replace”/“append” (default: “replace”) |
overwrite_types | Overwrite the column types | {'column1': 'INTEGER', 'column2': 'VARCHAR'} |
unique_conflict_method | How to handle the conflict on unique constrants. | ‘UPDATE’ (default: None) |
unique_constraints | The unique constraints of the table. | [‘col1’, ‘col2’] (default: None) |
Pro-only fields
Only in Mage Pro.Try our fully managed solution to access this advanced feature.
Field name | Description | Example values |
---|---|---|
batch_write | Use COPY command to write to PostgreSQL to improve performance. | True/False (default: False) |