> ## Documentation Index
> Fetch the complete documentation index at: https://docs.mage.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# PostgreSQL
## Configuration
To set up the PostgreSQL source, provide the following connection parameters:
| Key | Description | Sample Value |
| ------------------ | ---------------------------------------------------------------------- | ------------ |
| `database` | Name of the database you want to extract data from. | `demo` |
| `host` | Hostname of your PostgreSQL database. | `db.bit.io` |
| `password` | Password for the PostgreSQL user. | `abc123...` |
| `port` | Port number where the database is accessible (typically `5432`). | `5432` |
| `schema` | Schema containing the tables you want to extract. | `public` |
| `username` | Username with permissions to read and write from the specified schema. | `guest` |
| `replication_slot` | Logical replication slot name for Change Data Capture (CDC). | `mage_slot` |
| `publication_name` | Logical replication publication name for CDC. | `mage_pub` |
### Optional Configuration
| Key | Description | Sample Value |
| ------------------- | ----------------------------------------------------------------------------------------------------- | ------------ |
| `batch_fetch_limit` | Number of rows to fetch per batch (default is 50,000). Adjust if your instance has sufficient memory. | `50000` |
## Why Use PostgreSQL as a Source in Mage?
* **Real-time change data capture (CDC)**: Capture and sync only changed rows.
* **Reliable large-scale data ingestion**: Load millions of rows efficiently.
* **Flexible schema support**: Work with multiple schemas in your PostgreSQL instance.
* **Secure and scalable**: Support for SSL connections, logical replication slots, and publication management.
## Change Data Capture (CDC)
Mage supports 2 types of change data capture with PostgreSQL:
1. Batch query
2. Log replication
### Batch query
Mage will query PostgreSQL in batches using `SELECT`, `WHERE`, and `ORDER BY`
statements.
### Log replication
Mage supports **Change Data Capture (CDC)** for PostgreSQL using **logical replication**,
enabling real-time tracking of inserts, updates, and deletes without requiring full table reloads.
By reading PostgreSQL’s Write-Ahead Logs (WAL), Mage captures changes efficiently and applies them to the
destination system — inserting new records, updating existing ones, or deleting as needed —
ensuring low-latency, incremental data synchronization.
#### Setup in PostgreSQL
1. Enable logical replication in Postgres config
1. Local Postgres
1. Open the `postgresql.conf` file. Here is an example location on Mac OSX:
`/Users/Mage/Library/Application Support/Postgres/var-14/postgresql.conf`.
2. Under the settings section, change the value of `wal_level` to `logical`. The line in your
`postgresql.conf` file should look like this:
```text theme={"system"}
wal_level = logical
```
3. Restart the PostgreSQL service or database. You can do this via the PostgreSQL app or if you’re
on Linux, run the following commands:
```bash theme={"system"}
sudo service postgresql stop
sudo service postgresql start
```
4. Run the following query in your PostgreSQL database: `SHOW wal_level`. The result should be:
| `wal_level` |
| ----------- |
| `logical` |
2. AWS RDS/Aurora
1. Follow this [doc](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html#AuroraPostgreSQL.Replication.Logical.Configure)
to set up PostgreSQL logical replication for an Aurora PostgreSQL DB cluster
2. Logical replication for AWS RDS (PostgreSQL) can be set up in a similar way with the Aurora cluster.
3. Azure Database for PostgreSQL
1. Azure Database for PostgreSQL supports logical replication for **Flexible Server** and **Single Server** deployments.
2. Follow this [Azure guide](https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical-replication) to enable logical replication on Azure PostgreSQL Flexible Server.
3. Make sure the following parameters are set in the Azure portal:
* `wal_level` = `logical`
* `max_replication_slots` ≥ 1
* `max_wal_senders` ≥ 1
4. Grant replication privileges to your user:
```sql theme={"system"}
ALTER ROLE WITH REPLICATION;
```
5. Azure also requires that you configure firewall rules to allow your Mage server or IP range to access the PostgreSQL server.
6. For more detailed instructions, see: [Set up logical replication on Azure PostgreSQL](https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical-replication).
2. Run the following command in PostgreSQL to create a replication slot:
```sql theme={"system"}
SELECT pg_create_logical_replication_slot('mage_slot', 'pgoutput');
```
The replication slot name `mage_slot` quoted is used as an example in the guide. The actual replication slot name should be unique for each pipeline source.
The result should looking something like this:
| `pg_create_logical_replication_slot` |
| ------------------------------------ |
| `(mage_slot,0/51A80778)` |
3. Create a publication for all tables or for 1 specific table using the following commands:
```sql theme={"system"}
CREATE PUBLICATION mage_pub FOR ALL TABLES;
```
`mage_pub` is used in Mage’s [code](https://github.com/mage-ai/mage-ai/blob/master/mage_integrations/mage_integrations/sources/postgresql/__init__.py#L126)
or for 1 table:
```sql theme={"system"}
CREATE PUBLICATION mage_pub FOR TABLE some_schema.some_table_name;
```
Replace `some_schema` with the schema of the table and `some_table_name` with the name
of the table you want to replicate.
Run the following command to add or remove a table from publication.
```sql theme={"system"}
ALTER PUBLICATION mage_pub ADD/DROP TABLE table_name;
```
4. Verify that the publication was created successfully by running the following command in PostgreSQL:
```sql theme={"system"}
SELECT * FROM pg_publication_tables;
```
The result should looking something like this:
| `pubname` | `schemaname` | `tablename` |
| ---------- | ------------ | ----------- |
| `mage_pub` | `public` | `users` |
5. Grant the database user permission to read the replication slot:
```sql theme={"system"}
ALTER ROLE WITH REPLICATION;
```
#### Create data integration pipeline in Mage
Follow this [guide to create a data integration pipeline](/guides/data-integration-pipeline)
in Mage.
However, choose PostgreSQL as the source and choose `LOG_BASED` as the
replication method.
#### Testing pipeline end-to-end
Once you’ve created the pipeline, add a few rows into your PostgreSQL table that you just
created a logical replication for.
You can use the `INSERT` command to add rows. For example:
```sql theme={"system"}
INSERT INTO some_schema.some_table_name
VALUES (1, 2, 3)
```
Replace `some_schema` with the schema of the table and `some_table_name` with the name
of the table you want to replicate.
Change the `VALUES` to match the columns in your table.
##### Verify replication logs being created
Run the following commands in PostgreSQL to check for new logs:
```sql theme={"system"}
SELECT
*
FROM pg_logical_slot_peek_binary_changes('mage_slot', null, null, 'proto_version', '1', 'publication_names', 'mage_pub');
```
##### Run sync
After you added a few new rows, [create a trigger](/guides/triggering-pipelines#trigger)
to start running your pipeline and begin syncing data.
## What is PostgreSQL?
[PostgreSQL](https://www.postgresql.org/) is an open-source, enterprise-grade relational database system. Known for its robustness, extensibility, and SQL compliance, PostgreSQL powers thousands of applications worldwide in both small businesses and large enterprises.
Common use cases include:
* Transactional applications
* Analytical workloads
* Hybrid OLTP + OLAP systems
* Data warehousing
* Business intelligence (BI) and reporting pipelines