Re: Copy & Re-copy of DB

From: Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com>
To: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
Cc: sivapostgres(at)yahoo(dot)com, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>, "Ray O'Donnell" <ray(at)rodonnell(dot)ie>
Subject: Re: Copy & Re-copy of DB
Date: 2021-01-22 15:34:09
Message-ID: CAD+mzox6rwny6kKWf6y3KthfF72dsi3Muo6vsu2Gd_zDvG=u5Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

No. Just no. I is fine to make stored procedure changes in a development
environment and deploy them as part of a release. Typically you would want
some sort of change tracking software like alembic or squitch or something
like that. Production databases typically contain a huge amount of data or
data that you really shouldn't share.

Part of the release is to deploy changes to production systems. Often you
will want to back up those systems before a release in case you have to
roll back or just make small changes that you can revert. I would say that
release procedures for database deployment is well beyond the scope of this
list and every company I have worked for has different procedures.

Basically, the OP wanted to replicate a process that sort of works in MsSQL
kind of, maybe. The set of steps outlined will dump all of the tables and
restore them. If this is their process, I highly question that process but
those steps are correct. I would point out that eventually that system will
break down, is highly dependant on individuals knowing lot of steps,
possibly exposes data to people who shouldn't have it, is overly
complicated, probably isn't best practices for releases, and is error prone
(someone makes a change that no one else knows about and it breaks a
webpage).

So is this the best? In my opinion, probably not. Will it work in the way
that the OP wanted it to work? Yes. I simply wouldn't manage a process like
this but if that is the process that the OP is comfortable with and a lot
of people agreed to, it will work. Sometimes it is easier to simply
replicate the existing bad process that a team agrees to rather than making
a better process.

Thanks,
Ben

On Fri, Jan 22, 2021, 9:21 AM Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
wrote:

> On 22/01/21, Benedict Holland (benedict(dot)m(dot)holland(at)gmail(dot)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(at)yahoo(dot)com <
> > > sivapostgres(at)yahoo(dot)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
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Zwettler Markus (OIZ) 2021-01-22 15:37:37 open service broker api for local PGDG Postgres
Previous Message Zwettler Markus (OIZ) 2021-01-22 15:22:06 AW: ldap connection parameter lookup