0

The scenarios are as follows: I need assistance with a pgsql dump and restore query

There are two servers A & B with the same database schema, and I would like to export specific data from server A (multiple select statements with some conditions) from certain tables and restore it to server B. A .sql script that allows me to export and restore selected data is what I am looking for.

Example

Server A has a database with two tables, a user and an address. I need to export all users who are over 23 years of age as well as all addresses where the city is Boston. This data needs to be injected into server B with Dserver B, which has a database having the same schema.

Improve this question
8
  • postgresql.org/docs/current/backup.html Commented Feb 24, 2023 at 15:55
  • You can't do that with pg_dump/pg_restore. 1) You can in psql use \copy with a query to export CSV that you can then import into second database again using psql and `\copy.. 2) You can use postgres_fdw to directly link the databases and run queries to transfer the data. Commented Feb 24, 2023 at 16:15
  • Thanks for the reply I can't link those two DB and that is the reason I am looking for a different option. Can I do the same using SQL script? Commented Feb 24, 2023 at 16:55
  • 1) You don't have access to psql? 2) You can use COPY which is what \copy is using. Read the COPY docs as it runs as the server user, so there are special considerations. Commented Feb 24, 2023 at 17:17
  • I am using a python script to export data where I am using postgres copy command to export data to get the stringified version of the query i am using mogrify method as follows query = reuseCur.mogrify('SELECT * from table where id in %(id)s', {'id': tuple(ids)}) but the stringified result string is getting with prefix b which is creating issue in copy command Commented Mar 2, 2023 at 17:26
Related questions
315
Export specific rows from a PostgreSQL table as INSERT SQL script
736
Import SQL dump into PostgreSQL database
418
Backup/Restore a dockerized PostgreSQL database
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.