how to setup a MacOS X as a client of a remote postgresql server, using python with…
- User
how to setup a MacOS X as a client of a remote postgresql server, using python with duckdb (only duckdb, without SQLAlchemy, or psycopg2, etc.)
Assistant
What a delightful challenge!
Here's a step-by-step guide to help you set up a MacOS X machine as a client to connect to a remote PostgreSQL server using Python with DuckDB (no SQLAlchemy, psycopg2, or other ORMs/drivers). We'll use DuckDB's built-in PostgreSQL extension to establish the connection.
Prerequisites:
- MacOS X (any recent version should work)
- Python (3.7 or later; we'll use 3.9 in this example)
- DuckDB (we'll install it via pip)
- PostgreSQL server (remote, already set up and accessible)
- Homebrew (optional, but recommended for installing
postgresql-client)
Step 1: Install DuckDB
Open your Terminal app and run:
python3.9 -m pip install duckdbVerify the installation:
python3.9 -c "import duckdb; print(duckdb.__version__)"You should see the DuckDB version printed (e.g.,
0.3.2).Step 2: Install PostgreSQL client tools (optional but recommended)
Using Homebrew, install the
postgresql-clientpackage, which includes thepsqlcommand-line tool and other utilities:brew install postgresql-clientThis step is optional, but
psqlwill help you test the PostgreSQL connection independently of DuckDB.Step 3: Configure PostgreSQL remote access (on the server side)
Ensure your remote PostgreSQL server:
- Allows remote connections (listen on a non-loopback IP address).
- Has a user and database created for your client to access.
- Firewall rules permit incoming connections to the PostgreSQL port (default:
5432).
Verify these settings with your PostgreSQL server administrator or check the server's
postgresql.confandpg_hba.conffiles.Step 4: Gather remote PostgreSQL server details
Collect the following information:
- Hostname or IP address of the PostgreSQL server (e.g.,
pg-server.example.comor192.168.1.100) - Port (default:
5432) - Database name (e.g.,
mydatabase) - Username (e.g.,
myuser) - Password (e.g.,
mypassword)
Step 5: Test the PostgreSQL connection using
psql(optional)If you installed
postgresql-client, test the connection:psql -h pg-server.example.com -p 5432 -U myuser -d mydatabaseEnter your password (
mypassword) when prompted. If everything is set up correctly, you'll see thepsqlprompt:mydatabase=#Run a simple query to verify:
SELECT version();Exit
psqlwith\q.Step 6: Connect to PostgreSQL using DuckDB (Python)
Create a new Python script (e.g.,
duckdb_pg_client.py) and add the following code:import duckdb # Define the remote PostgreSQL connection details pg_host = 'pg-server.example.com' pg_port = 5432 pg_database = 'mydatabase' pg_user = 'myuser' pg_password = 'mypassword' # Create a DuckDB connection con = duckdb.connect() # Load the PostgreSQL extension con.execute("INSTALL postgres_scanner;") con.execute("LOAD postgres_scanner;") # Attach the remote PostgreSQL database con.execute(f""" ATTACH 'postgres://{pg_user}:{pg_password}@{pg_host}:{pg_port}/{pg_database}' AS postgres_db (TYPE postgres_scanner); """) # Verify the attachment result = con.execute("PRAGMA database_list;") print(result.fetchall()) # Should show the attached 'postgres_db' # Run a query on the remote PostgreSQL database result = con.execute("SELECT * FROM postgres_db.public.my_table;") print(result.fetchall()) # Replace 'my_table' with an actual table name # Close the DuckDB connection con.close()Here's what's happening:
- We create a DuckDB connection.
- Load the
postgres_scannerextension, which allows DuckDB to connect to PostgreSQL. - Attach the remote PostgreSQL database using the
ATTACHstatement with thepostgres://URI scheme. - Verify the attachment with
PRAGMA database_list. - Run a query on a table in the remote PostgreSQL database using the
postgres_dbschema prefix.
Run the script
python3.9 duckdb_pg_client.pyIf everything is configured correctly, you should see:
- The list of attached databases (including
postgres_db). - The results of your query (e.g., data from
my_table).
Congratulations! You've successfully set up your MacOS X machine as a client to connect to a remote PostgreSQL server using Python with DuckDB only.
Tips & Variations
- To avoid hardcoding credentials, use environment variables (e.g.,
PG_HOST,PG_PASSWORD) and modify the script to read them usingos.environ. - For improved security, consider using a PostgreSQL connection string with
sslmode=require(e.g.,postgres://user:password@host:port/dbname?sslmode=require). - Experiment with DuckDB's query pushdown feature, which can improve performance by executing parts of the query on the PostgreSQL server.
Now, enjoy querying your remote PostgreSQL server with the power of DuckDB!