Transferring tables from one db to another in postgresql?

Question

PostgreSQL

Good day everyone!
How to transfer the table schema to another database?

For example, db1 has a table_user with hundreds of data, but db2 doesn't. You need table_user to go to db2, but without data, you just need to create it.
And the second version of events: there is a table_user in db1 and db2, but 20 new columns appeared in db1 table_user, how to transfer them in an easier form to db2 table_user?

Those. the bottom line is that the development of the database will go locally, but in the case of deployment, the database takes on a new look and does not erase its data.
In Google I find solutions for a complete transfer of the database, overwriting completely, which does not suit me.

Please help, is there any info where I can read about it. I will be grateful for any help!

Answer 1

So, the question can actually lead to "how to build a process for making changes to the database so that everything does not accidentally ruin and hurt."
1. you can use tools to generate a diff a la https://github.com/djrobstep/migra
diffs are added along with the pr, if the scheme has been changed while you are working, you roll the diffs locally, generate a new diff and add it to the pr.
2. to automate the rolling of differentials on the base - you can use flyway (or any other analogue). the essence is simple - it keeps a history of completed migrations, when the application starts, it applies all new ones in the correct order
3. at the moment when there is a lot of data - it loses its relevance. in practice, all migrations are still performed manually (i.e. not run-and-forget). (further my experience concerns more Mayskl, because in postgres we don't have multi-terabyte tables) the main problem here is table changes = lock = outage, which should not be allowed. therefore, in order to make changes, you need to create a new table with a new schema, pour all the old data into it, synchronize all the new data, swap tables and drop the old one. the percon and github toolkit (pt-online-schema-change and gh-ost) helps here, which automate all this. But both of them are sharpened for Microsoft, for postgres you need to search and test (well, or maybe someone will clarify)

Answer 2

In general, you need to learn how
a) queries that modify the database architecture (Create table, Alter Table, Create index, etc.) differ
from queries that work with data from the current architecture (SELECT, INSERT, UPDATE , DELETE).
Having exported the database in sql format, you will find that the dump consists of queries that first create or modify the database architecture, and why, there are queries that insert data.

For example, db1 has a table_user with hundreds of data, but db2 doesn't. You need table_user to go to db2, but without data, you just need to create it.

For this task, it is enough to take a db1 dump of the database or even a separate table table_user, and execute only those queries from the dump that do not concern inserting data into table_user, and other tables of schema objects.

And the second version of events: there is a table_user in db1 and db2, but 20 new columns appeared in db1 table_user, how to transfer them in an easier form to db2 table_user?

It's more difficult here.
Learn alter table add column and apply it to the missing columns. Do not forget to take requests from the dump that create or modify other objects associated with new columns.