# PostgreSQL Database
target-postgres is a loader that moves data into a PostgreSQL (same as Postgres) database. In order to use this target, you will need a Postgres database where Meltano can load data.
# Beginner: Using a PostgreSQL Database for the First Time
In this section, we provide a tutorial for installing Postgres and setting up a new Postgres database.
# Install PostgreSQL locally
- Launch your terminal
- Follow the installation instructions from PostgreSQL.org
# Create Your Database
- Start your PostgreSQL server
- Create your first database by running
- Connect to your postgres with the command:
# Create Your User
createuser -s postgres- this fixes the error: role "postgres" does not exist
ALTER USER postgres WITH PASSWORD 'password';with the new password of your choice
\duto get a list of users (from within psql)
# Install pgAdmin
- Go to the pgAdmin website and download the GUI https://www.pgadmin.org/
- Install the .dmg packge that is appropriate for your operating system
- Run the pgAdmin application and follow the prompt to create a master password
- Back on the command line, log into your postgres user with
psql -U postgres(by default, Postgres will log you into your username account on your machine so you will need to switch to the new user we created in the steps above)
- Navigate to pgAdmin in your browser (it will launch a new tab when the server starts at a URL similar to http://127.0.0.1:52991/browser/#
- On the left hand navigation bar, you should see your database listed under servers. If you don't, click "Add Server" to connect your database to pgAdmin.
# Intermediate: Connecting Meltano to an Existing PostgreSQL Database
Once you have identified a PostgreSQL database where Meltano should load the data it extracts from your source(s), add the
target-postgres loader to Meltano:
cd my_project meltano add loader target-postgres
You can now configure target-postgres to use your PostgreSQL database
# Advanced: Command Line (CLI) Configuration
In this section we provide additional information for configuring Meltano to connect with your PostgreSQL database from the Meltano command line interface (CLI).
- Create a
.envfile in your project directory if it doesn't exist already
- Open your project's
.envfile in a text editor
- Add the following variables to your file:
export PG_ADDRESS="" export PG_USERNAME="" export PG_PORT="" export PG_PASSWORD="" export PG_DATABASE="" # export PG_URL="" # alternatively specify a postgresql:// connection URL # export PG_SCHEMA="" # override if the default (see below) is not appropriate
Enter the appropriate values for each variable inside the quotes.
The default value for
$MELTANO_EXTRACT__LOAD_SCHEMA, which will expand to the value of the
load_schema extra for the extractor used in the pipeline, which defaults to the extractor's namespace, e.g.
If you are running Meltano UI (
meltano ui), you will need to restart it for the changes to take effect.