On 22/01/21, Benedict Holland (benedict.m.holland@gmail.com) wrote:
> I mean... the best? You just laid out a bunch of steps to define a process.
>
> > On Thu, Jan 21, 2021 at 11:12 PM sivapostgres@yahoo.com <
> > sivapostgres@yahoo.com> wrote:
> >
> >> So the solution to the issue will be
> >>
> >> 1. Backup a DB using PGDUMP from 1st server.
> >> 2. Restore the DB in 2nd server.
> >> 3. Make required changes in the 2nd server.
> >> 4. Backup that DB using PGDUMP from 2nd server.
> >> 5. Delete / Rename that DB in the 1st server
> >> 6. Restore that DB in the 1st server.
> >> 7. Work again in the 1st server.
> >>
> >> Is this the best way to carry out this process?
Rather late to the party, and I expect this has been mentioned already,
but presumably changes to any database are either to do with the data or
to do with aspects such as the pl functions.
Data transformations can be tested in production and testing in
transactions, so that the logic of the transformation can be captured in
a set of SQL statements which can be applied to either environment
through a (possibly automated) revision control system.
Data insertions are much more conveniently only done on the production
database, as converging data between different databases can be tricky.
However if your data is conveniently added in bulk without the risk of
duplication, a revision control approach could also work.
Finally working on pl functions and similar changes we do out of
revision control. In other words, "if it works in testing we apply it to
production". Of the the three only this is idempotent.
In any event, perhaps the following could work?
1. Backup the DB on the 1st server using pg_dump
2. Restore the DB on the 2nd server
3. Record changes on the 2nd server as revision controlled statements
4. Replay changes on the 1st server using the revision controlled
statements
Regards
Rory