I have a Django app, the database for which is under active manual development (it's a language-learning app, so it stores vocabulary, grammatical concepts, etc). I'd prefer to do that development in my local django/postgres environment.
However, I don't want to be constantly wiping out the User table from the live version!
I'm very new to Postgres, so please don't assume I know what I'm doing here - would some kind of schema be the right approach here?
To just backup the one table, use COPY from inside the database:
COPY user_tbl TO '/path/to/file';
or pg_dump from the shell:
pg_dump -t user_tbl mydb > user_tbl.sql
Then drop the database, restore your new version, empty user_tbl and use COPY FROM to restore the one table:
COPY user_tbl FROM '/path/to/file';
or restore the backup with the one table from the shell with psql:
psql -f user_tbl.sql mydb
There is no such thing as "COPY ... CASCADE". The simplest method to identify depending tables would be to start a transaction, call TRUNCATE tbl CASCADE and record the notices you get:
BEGIN;
TRUNCATE user_tbl CASCADE;
NOTICE: truncate cascades to table "tbl1"
NOTICE: truncate cascades to table "tbl2"
NOTICE: truncate cascades to table "tbl3"
Then roll back the transaction - so nothing actually changes:
ROLLBACK;
Careful with that. If you COMMIT the truncate goes through.
Well, not actually "slow", but the code is a lot more complex. This doesn't take an exclusive lock on the involved tables, though, so it's a lot cleaner and safer:
WITH RECURSIVE x AS (
SELECT conrelid::regclass
FROM pg_constraint
WHERE confrelid = 'user_tbl'::regclass
UNION
SELECT p.conrelid::regclass
FROM x
JOIN pg_constraint p ON p.confrelid = x.conrelid
)
SELECT conrelid::text AS tbl
FROM x;
Returns:
tbl
------
tbl1
tbl2
tbl3
I use a recursive CTE (requires PostgreSQL 8.4 or later) on the catalog table pg_constraint, because each table can have dependencies in turn.
Use UNION, not UNION ALL to avoid multiple evaluation of tables that might be linked with multiple foreign keys directly or indirectly.
