[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Copy & Re-copy of DB
From: Benedict Holland <benedict.m.holland () gmail ! com>
Date: 2021-01-23 17:03:45
Message-ID: CAD+mzoxa4HKd2AKtEDpT3=kH_JMFxD0XvKhcz56xq2axT61QLA () mail ! gmail ! com
[Download RAW message or body]
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 Always
> BKR Sivaprakash
>
> On Friday, 22 January, 2021, 09:28:13 pm IST, Rory Campbell-Lange
> <rory@campbell-lange.net> <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.
>
[Attachment #3 (text/html)]
<div dir="auto">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. <div \
dir="auto"><br></div><div dir="auto">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. </div><div \
dir="auto"><br></div><div dir="auto">Good luck!</div><div \
dir="auto">Ben</div></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Sat, Jan 23, 2021, 10:17 AM Ron <<a \
href="mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
On 1/23/21 6:52 AM, <a href="mailto:sivapostgres@yahoo.com" target="_blank" \
rel="noreferrer">sivapostgres@yahoo.com</a> wrote:<br> <blockquote type="cite">
<div style="font-family:times new roman,new york,times,serif;font-size:16px">
<div dir="ltr">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. </div>
<div dir="ltr"><br>
</div>
<div dir="ltr">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. </div>
<div dir="ltr"><br>
</div>
<div dir="ltr">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. </div>
<div dir="ltr"><br>
</div>
<div dir="ltr">Portability is the need for
them. <br>
</div>
</div>
</blockquote>
<br>
Sadly, the architecture of Postgres means that there's no concept of
detaching <b>a single database</b>. <br>
<br>
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,<br>
<br>
TBH, tarring data/ isn't really necessary.<br>
<br>
<blockquote type="cite">
<div style="font-family:times new roman,new york,times,serif;font-size:16px">
<div dir="ltr">Happiness Always</div>
<div dir="ltr">BKR Sivaprakash</div>
<div dir="ltr"><br>
</div>
</div>
<div id="m_4921246812034265651yahoo_quoted_1604458094">
<div style="font-family:'Helvetica \
Neue',Helvetica,Arial,sans-serif;font-size:13px;color:#26282a"> <div> On Friday, \
22 January, 2021, 09:28:13 pm IST, Rory
Campbell-Lange <a href="mailto:rory@campbell-lange.net" target="_blank" \
rel="noreferrer"><rory@campbell-lange.net></a> wrote: </div> <div><br>
</div>
<div><br>
</div>
<div>On 22/01/21, Benedict Holland (<a shape="rect" \
href="mailto:benedict.m.holland@gmail.com" target="_blank" \
rel="noreferrer">benedict.m.holland@gmail.com</a>) wrote:
<div id="m_4921246812034265651yqtfd24425"><br clear="none">
> Sometimes it is easier to simply > replicate the
existing bad process<br clear="none">
> that a team agrees to rather than making > a
better process.</div>
<br clear="none">
<br clear="none">
As Alvar Aalto said in a lecture at MIT<br clear="none">
<br clear="none">
It is not by temporary building that Parthenon comes on
Acropolis.</div>
</div>
</div>
</blockquote>
<br>
<div>-- <br>
Angular momentum makes the world go 'round.</div>
</div>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic