Is there a way to filter pg_dump by timestamp for PostgreSQL?

Question

I have a database that needs backing up, but only for specific timestamps and tables, like from the first of October to the 15th of October. After looking up on multiple sites, I have not found any methods that can suit my requirements.

Let's say I have database_A, and database A has 15 tables. I want to be able to use pg_dump to back up 10 tables from database_A, from the 1st of October to the 15th of October, all into 1 file. Below is what I have managed to do, but have not gotten the date portion yet as I'm not entirely sure.

pg_dump -U postgres -t"\"table_1\"" -t"\"table_2\"" database_A > backup.csv

This above code will work if I want to back up multiple tables into one file, and it will back up the entire table, from start to end.

I would much appreciate if someone could help me with this, as I am still mostly a beginner at this. Thank you!

If the options of the pg_dump can't implement the data selection on timestamps, then you can use the COPY instead see the manual. — Edouard
– Edouard, Commented Nov 2, 2021 at 10:15

pgraham · Accepted Answer · 2022-08-26 22:32:04Z

If the data you're copying has a column named timestamp you can use psql and the COPY command to accomplish this:

# Optional: clear existing table since COPY FROM will append data psql -c "TRUNCATE TABLE my_table" target_db psql -c "COPY (SELECT * FROM my_table WHERE timestamp >= '...' AND timestamp <= '...') TO STDOUT" source_db | psql -c "COPY my_table FROM STDIN" target_db

You can repeat this pattern for as many tables as necessary. I've used this approach before to copy a subset of live data into a development database and it works quite well, especially if you put the above commands into a shell script.

Collectives™ on Stack Overflow

Is there a way to filter pg_dump by timestamp for PostgreSQL?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related