Обсуждение: Re: Copy & Re-copy of DB

On 21/01/2021 13:13, sivapostgres@yahoo.com wrote:
> Hello,
>
> I'm from SQL Server now developing my current project using PG.
>
> In our earlier project(s), we used to copy the database from one server
> to another, work in another server, and re-copy it in the original
> server.   All happened easily with just transferring the two files (mdf
> & ldf) to our required server.
>
> Want to replicate the same in PG.  Is there any way to accomplish the
> same in PG ?

create database <new-db-name> template <old-db-name>

Would that do the job? You could also use pg_dump.

Ray.


--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

create database is to create a new database.  If we switch to new database, we need to change the new databasename in the program(s) that access this database.  

Is there any way to overwrite the data ?

On Thursday, 21 January, 2021, 07:12:19 pm IST, Ray O'Donnell <ray@rodonnell.ie> wrote:

On 21/01/2021 13:13, sivapostgres@yahoo.com wrote:

> Hello,
>
> I'm from SQL Server now developing my current project using PG.
>
> In our earlier project(s), we used to copy the database from one server
> to another, work in another server, and re-copy it in the original
> server.   All happened easily with just transferring the two files (mdf
> & ldf) to our required server.
>
> Want to replicate the same in PG.  Is there any way to accomplish the
> same in PG ?

create database <new-db-name> template <old-db-name>

Would that do the job? You could also use pg_dump.

Ray.


--
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

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?

Well I don't know if it's best or not.  But you said you wanted:

to copy the database from one server to another, work in another server, and re-copy it in the original server.

(and additionally end up with the database having the same name on the original server.)

So your steps do seem like they would be one way to accomplish that.  What counts as the "best" way would depend on what else is important to you about this process, i.e. what criteria would you use for best?

Cheers,
Ken

 

--

AGENCY Software  

A Free Software data system

By and for non-profits

http://agency-software.org/

https://demo.agency-software.org/client

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.

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

On 1/23/21 6:52 AM, sivapostgres@yahoo.com wrote:

We are an ISV.   I agree the risk involved in sharing the data.  Still few of my customers need that facility and are accustomed to it when using SQL Server.   On switch over to PG, I face this issue as a limitation. Need to find and provide a solution.  

For those customers, having good volume of data, we're implementing replication which resolves this issue.   For smaller sized database (company(ies)), they prefer (and we too) this copy and re-copy procedure, to transfer the data between home and office.  

And this pandemic made this a compulsory feature, which they don't want to loose.  This transfer is not a one time job, it gets repeated, which they have been doing for years.  Here security is not a big concern for them.  

Portability is the need for them. 

Sadly, the architecture of Postgres means that there's no concept of detaching a single database. 

If you only have one database in the "cluster" (ancient Postgres term for "instance"), then you can stop the cluster "-m smart", tar up data/, and transfer it across.  You'll need to have a directory on your dev server, custom postgresql.conf (that among other things uses a different port number) and pg_hba.conf files,

TBH, tarring data/ isn't really necessary.

Happiness Always

BKR Sivaprakash

On Friday, 22 January, 2021, 09:28:13 pm IST, Rory Campbell-Lange <rory@campbell-lange.net> wrote:

On 22/01/21, Benedict Holland (benedict.m.holland@gmail.com) wrote:

> Sometimes it is easier to simply > replicate the existing bad process
> that a team agrees to rather than making > a better process.

As Alvar Aalto said in a lecture at MIT

    It is not by temporary building that Parthenon comes on Acropolis.

--
Angular momentum makes the world go 'round.