Working with Remote Databases

Working with Remote Databases

Connecting to Remote Databases

You can connect to remote PostgreSQL databases (e.g., Neon, AWS RDS, etc.) using connection strings:

macOS/Windows:

psql "postgresql://username:password@host:port/database?sslmode=require"

Example (Neon database):

psql "postgresql://neondb_owner:npg_IHplxd8K5VPG@ep-broad-mud-aewh2sue-pooler.c-2.us-east-2.aws.neon.tech/neondb?sslmode=require&channel_binding=require"

Using connection parameters:

psql -h hostname -p port -U username -d database

Pulling/Copying Remote Database to Local

To copy a remote database to your local PostgreSQL instance:

Step 1: Create Local Database

# macOS
createdb zooly2_local

# Windows
psql -U postgres -c "CREATE DATABASE zooly2_local;"

Step 2: Copy Remote Database to Local

Using pg_dump and psql:

# Dump remote database and pipe to local database
pg_dump --no-owner "postgresql://remote_user:password@remote_host:port/remote_db?sslmode=require" | \
psql "postgresql://postgres:password@localhost:5432/zooly2_local?sslmode=disable"

Example with Neon:

pg_dump --no-owner "postgresql://neondb_owner:npg_IHplxd8K5VPG@ep-broad-mud-aewh2sue-pooler.c-2.us-east-2.aws.neon.tech/neondb?sslmode=require&channel_binding=require" | \
psql "postgresql://postgres:password@localhost:5432/zooly2_local?sslmode=disable"

Using intermediate file:

# Step 1: Dump remote database to file
pg_dump --no-owner "postgresql://remote_user:password@remote_host:port/remote_db?sslmode=require" > remote_backup.sql

# Step 2: Restore to local database
psql "postgresql://postgres:password@localhost:5432/zooly2_local?sslmode=disable" < remote_backup.sql

Step 3: Enable pgvector Extension (if needed)

After copying the database, ensure pgvector is enabled:

psql -U postgres -d zooly2_local -c "CREATE EXTENSION IF NOT EXISTS vector;"

Copying Local Database to Remote

To copy your local database to a remote server:

pg_dump --no-owner "postgresql://postgres:password@localhost:5432/zooly2_local?sslmode=disable" | \
psql "postgresql://remote_user:password@remote_host:port/remote_db?sslmode=require"