0

Is there a way to backup only the schemas (plus their relative data) of a PostgreSQL DB individually, into single dedicated files?

I'm on Linux CentOS 7, hopefully there's some way to automate this so I can make a cronjob and run the backups when I want!

I know pg_dump, I've checked and with it I think I can make a single file backup of all the schemas + data with this command:

pg_dump dbname -n -f backup.sql 

but what I want is different, I want to separate all the schemas + data of the DB in single files. I have no idea how to achieve that with pg_dump... maybe using bash too? But I have no idea, I have never used bash in my life.

--schema=PATTERN         dump the specified schema(s) only

I think that still saves everything into one single file. Also, I don't want to specify all the schemas by hand, as they are a lot... I need them all, so I don't think I have to use --schema=PATTERN, right?

4
  • --schema=PATTERN dump the specified schema(s) only
    – user1822
    Mar 27, 2023 at 10:10
  • thanks... but I think it still saves everything into one single file. Also, I don't want to specify all the schemas by hand, as they are a lot... I need them all, so I don't think I have to use "--schema=PATTERN", right? Mar 27, 2023 at 10:12
  • You'll have to script that: first, query for all the schemas you care for, then run a pg_dump for each of them. Mar 28, 2023 at 15:19
  • Yeah I've solved that with a small script, wasn't as hard as I thought... I was a little bit scared by the other big scripts in multiple files I've seen online ahah, thank you both! Mar 29, 2023 at 15:48

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.