Postgres CSV dumper

postgres_csv_dump.py

import json
import sys
import urllib.parse
from pathlib import Path

import psycopg2

url = sys.argv[1]
res = urllib.parse.urlparse(url)
conn = psycopg2.connect(
    database=res.path[1:],
    port=res.port,
    user=res.username,
    host=res.hostname,
    password=res.password,
)
cur = conn.cursor()

cur.execute("SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public';")
tables = [".".join(table) for table in cur.fetchall()]

dump_folder = Path("dump")
dump_folder.mkdir(exist_ok=True)

cur.execute(
    "SELECT conrelid::regclass AS src_table, \
        confrelid::regclass dest_table \
    FROM pg_constraint \
    WHERE contype = 'f' AND connamespace = 'public'::regnamespace"
)
fkeys = cur.fetchall()
deps = dict([(table, []) for table in tables])
for src, dest in fkeys:
    deps[dest].append(src)

json.dump(deps, dump_folder.joinpath("deps.json").open("w"), indent=2)

for table in tables:
    # https://stackoverflow.com/a/49612587/320546
    table_path = dump_folder.joinpath(f"{table}.csv")
    sql = f"COPY (SELECT * FROM {table}) TO STDOUT WITH CSV HEADER DELIMITER ';'"
    with table_path.open("w") as file:
        cur.copy_expert(sql, file)