Обсуждение: Re: Copy & Re-copy of DB
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 ?
Limitation of Backup utility. Transfer to another server was done successfully. When we want to re-copy to original server, we have to delete the original db in the original server, create a new db, restore it. Is that the only way ? or any other elegant way available ?
Happiness Always
BKR Sivaprakash
In SQLServer each db has its own data file and transaction log file and hence can be copied the way you described.
> Limitation of Backup utility. Transfer to another server was done successfully. When we want to re-copy to original server,
>we have to delete the original db in the original server, create a new db, restore it. Is that the only way ?
> or any other elegant way available ?
what do you mean "we have to delete the original db". Did you mean the cluster as indicated in PGDATA dir. Please note
that when you do that you lose other databases also within the same cluster. If the cluster contains only one database then
you can simply overwrite PGDATA dir without creating a new 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
Pg_dump does what you want but you could also set up replication.
Thanks,
Ben
On Thu, Jan 21, 2021, 8:42 AM 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
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
> 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
Pg_dump and pg_restore are what you want. They create sql files that you pass around.
But seriously, if you have two servers running, replicate one or have your application point to a server address that you can direct via dns to whatever active one you want. Postgres makes replication simple. There are a lot of solutions for your problem but the 1:1 solution to copy over a single file and load it is pg_dump and pg_restore.
Thanks,
Ben
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
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.
If you are just concerned about having the same database name, what about renaming (ALTER DATABASE ... RENAME TO) the old and/or new databases so they end up the way you want?
Cheers,
Ken
Ken
![](https://agency-software.org/agency_logo_medium.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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?
Happiness Always
BKR Sivaprakash
On Friday, 22 January, 2021, 12:40:05 am IST, Ken Tanzer <ken.tanzer@gmail.com> wrote:
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.
If you are just concerned about having the same database name, what about renaming (ALTER DATABASE ... RENAME TO) the old and/or new databases so they end up the way you want?
Cheers,
Ken
Ken
![](https://agency-software.org/agency_logo_medium.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
So the solution to the issue will be1. 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 server6. 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
Ken
![](https://agency-software.org/agency_logo_medium.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
I mean... the best? You just laid out a bunch of steps to define a process. I will tell you that this sounds like a bad process and that there are easier ways to make changes like inside of a transaction or backing up a database before making changes or creating a table replication job, making changes to one server, and push it to the other one.
But you didn't want to do that. You wanted to create a single file for your database, load it into a server, make changes, and copy them back. I would say that this is an overly complicated and unnecessary process but if that is how you want to make changes then what you described is the set of steps.
Thanks,
Ben
On Fri, Jan 22, 2021, 2:35 AM Ken Tanzer <ken.tanzer@gmail.com> wrote:
So the solution to the issue will be1. 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 server6. 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 SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow 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
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@campbell-lange.net> wrote:
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 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.
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.
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.
> 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.
On 1/23/21 6:52 AM, sivapostgres@yahoo.com wrote:
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.
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 AlwaysBKR SivaprakashOn 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.
Angular momentum makes the world go 'round.
Right. Like, you can absolutely do this. If you need single tables, you can technically set pgdump to include or exclude tables. Be warned though that this is extremely dangerous as it will not copy over any linked tables and I don't think it does sequences associated with those tables either. Basically, you can get yourself into a huge heap of trouble by doing that but you can. I do that on some systems when I have huge data tables that no one cares about.
There are always outside factors for all of this. If this is how you figured out how to get people what they sort of want, you can do this but be aware of the many many risks associated with it regarding security, data management, and trying to sync databases using dump files.
Good luck!
Ben
On Sat, Jan 23, 2021, 10:17 AM Ron <ronljohnsonjr@gmail.com> wrote:
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 AlwaysBKR SivaprakashOn 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.