| From: | Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Move rows from one database to other |
| Date: | 2017-02-21 15:30:47 |
| Message-ID: | ff933a31-6aa2-8ebd-db5c-2af694f3aca1@evolu-s.it |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Il 21/02/2017 15:38, Melvin Davidson ha
scritto:</div>
<blockquote
cite="mid:CANu8FixqdzCZP4h3fPTfWxuhmXCmyfpcV3YtRDre=OoqPE8KkA(at)mail(dot)gmail(dot)com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><span
class="gmail-HOEnZb"><font color="#888888">
<div>
<div dir="ltr">
</div>
</div>
</font></span></blockquote>
</div>
Depending on how much data you want to move, and if the tables
have the same structure, you might also want to consider using<br>
</div>
<div class="gmail_extra">pg_dump -a<br>
<br>
</div>
<div class="gmail_extra">OR<br>
</div>
<div class="gmail_extra">multiple instances of <br>
<pre class="gmail-SYNOPSIS">on satellite
COPY { <tt class="gmail-REPLACEABLE gmail-c2">table_name</tt> [ ( <tt class="gmail-REPLACEABLE gmail-c2">column_name</tt> [, ...] ) ] | ( <tt class="gmail-REPLACEABLE gmail-c2">query</tt> ) }
TO { '<tt class="gmail-REPLACEABLE gmail-c2">filename</tt>' | PROGRAM '<tt class="gmail-REPLACEABLE gmail-c2">command</tt>' | STDOUT }
[ [ WITH ] ( <tt class="gmail-REPLACEABLE gmail-c2">option</tt> [, ...] ) ]
</pre>
<pre class="gmail-SYNOPSIS">on central
COPY <tt class="gmail-REPLACEABLE gmail-c2">table_name</tt> [ ( <tt class="gmail-REPLACEABLE gmail-c2">column_name</tt> [, ...] ) ]
FROM { '<tt class="gmail-REPLACEABLE gmail-c2">filename</tt>' | PROGRAM '<tt class="gmail-REPLACEABLE gmail-c2">command</tt>' | STDIN }
[ [ WITH ] ( <tt class="gmail-REPLACEABLE gmail-c2">option</tt> [, ...] ) ]
A BIG consideration is:
Does the Central DB have the same table structures as all satellite DB's?
Does the Central DB already have records in the tables.
Do all Satellite tables have unique records for each other?
</pre>
<pre class="gmail-SYNOPSIS">As Adrian stated, it would be very helpful if you provided us with all O/S and PostgreSQL vesions involved.
</pre>
</div>
</div>
</blockquote>
<br>
AFAIK pg_dump is version-independent (I use it to upgrade some small
clusters), given that you are using the one from the newest version
and you are dumping from old and restoring from new. <br>
Another thing that I saw is that pg_dump is less I/O stressful (I
can almost safely dump databases from my production server in
traffic hours, even if I never tried doing it in rush hours :-) )
and therefore slower than COPY, but on the other side, using COPY,
the risk is to have all I/O bandwidth eaten by data transfer,
especially if the amount of data to be transferred is considerable.
I tried some times to COPY across different clusters with different
versions, but I always had issues.<br>
<br>
My 2 cents.<br>
<br>
Cheers<br>
Moreno.<br>
<br>
<blockquote
cite="mid:CANu8FixqdzCZP4h3fPTfWxuhmXCmyfpcV3YtRDre=OoqPE8KkA(at)mail(dot)gmail(dot)com"
type="cite">
<div dir="ltr">
<div class="gmail_extra"><br>
-- <br>
<div class="gmail_signature">
<div dir="ltr"><font size="4"><b><span
style="font-family:courier new,monospace">Melvin
Davidson</span></b></font><br>
<font style="font-weight:bold" size="3"><span
style="color:rgb(128,0,255)">I reserve the right to
fantasize. Whether or not you </span><br
style="color:rgb(128,0,255)">
<span style="color:rgb(128,0,255)">wish to share my
fantasy is entirely up to you. </span><img
moz-do-not-send="true" style="color: rgb(128, 0,
255);"
src="http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif"></font><br>
</div>
</div>
</div>
</div>
</blockquote>
<p><br>
</p>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 4.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Thomas Güttler | 2017-02-21 15:53:12 | Re: Move rows from one database to other |
| Previous Message | Tom Lane | 2017-02-21 14:46:16 | Re: Different LEFT JOIN results with and without USING |
