0

I am migrating some data to another server while dropping all sequences/functions/views. When I try to do simply

DROP VIEW | SEQUENCE | FUNCTION | AGGREGATE without CASCADE, errors are thrown to indicate dependencies. The goal is that if no data (=tables?) will be affected by using CASCADE, then I can use it.

Assumption: if no table is dependent on views/functions/sequences, then no table will be affected by using CASCADE.

Then I guess using CASCADE for views will be fine since they always depend on some base tables.

For functions and sequences I am not very sure. I haven't written that many of sql functions myself and I don't know if the data in a table are generated by some function, whether it means the table depends on the function.

For sequences, it seems that a sequence will be generated automatically by postgres if I use serial as column type. My first guess would be that in this case, the table that is using serial depends on the sequence. However, by testing on a dummy database on my laptop, I found that the data seems to be unaffected. (I just use SELECT COUNT(*) FROM TABLE_NAME after dropping the sequence)

0

I am quite certain that no table would be dropped if you remove views, sequences and functions with the CASCADE option.

But parts of a table may vanish:

  • Indexes, triggers, rules, column defaults, check constraints and in PostgreSQL v12 generated columns may vanish if you drop the functions and operators used in them.

  • Column defaults will vanish with the sequences used in them.

Dropping views should not affect tables at all.

Disclaimer: I have probably forgotten one or two cases.

Why don't you just selectively dump the parts of the database that you need with the -l and -L options of pg_restore?

  • Does that mean if I don't specify my column value of serial type, it will be NULL? But in my case the column using serial also has the PRIMARY KEY constraint, so will it still have a default value at all? – kumom Jul 19 at 19:54
  • By the way, I didn't find the option -l or -L in the documentation of pg_dump – kumom Jul 19 at 19:54
  • No, the primary key column won't have a default value then. Sorry about the -l, I meant pg_restore (fixed in the answer). – Laurenz Albe Jul 19 at 19:56
  • Thank you! I didn't know -l and -L can be that useful. However, actually I am migrating the whole cluster, and there are too many datasets to inspect and filter manually. That's why I look for a more automatic solution (which yet probably does not lead to the best result) – kumom Jul 19 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.