Table Export & Import

On 1 April 2019 08:09:37 CEST, Sathish Kumar <satcse88@gmail.com> wrote:

Hi Team,

We have a requirement to copy a table from one database server to
another
database server. We are looking for a solution to achieve this with
lesser
downtime on Prod. Can you help us with this?

Table Size: 160GB
Postgresql Server Version: 9.5

You can take a dump at any time without downtime. Other solution would be a logical replicarion using, for instance, slony, londiste or pg_logical from 2ndQuadrant.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company

Hi
One solution could be to use intel technology: FPGA : https://www.intel.fr/content/www/fr/fr/products/programmable.html
the principle is to add an PCI electronic card on the server with CPUs and RAM.
this greatly speeds up the loading of the data into the database.

Best Regards

[cid:image002.png@01D14E0E.8515EB90]
[Certification-DALIBO]

Didier ROS
Expertise SGBD
EDF - DTEO - DSIT - IT DMA
Département Solutions Groupe
Groupe Performance Applicative
32 avenue Pablo Picasso
92000 NANTERRE

didier.ros@edf.fr<mailto:didier.ros@edf.fr>
Tél. : +33 6 49 51 11 88
[cid:image003.png@01D4BE20.1EAF68B0]<mailto:prenom.nom@edf.fr>[cid:image004.png@01D4BE20.1EAF68B0]<sip:prenom.nom@edf.fr>

De : satcse88@gmail.com [mailto:satcse88@gmail.com]
Envoyé : lundi 1 avril 2019 08:10
À : pgsql-general@postgresql.org >> PG-General Mailing List <pgsql-general@postgresql.org>
Objet : Table Export & Import

Hi Team,

We have a requirement to copy a table from one database server to another database server. We are looking for a solution to achieve this with lesser downtime on Prod. Can you help us with this?

Table Size: 160GB
Postgresql Server Version: 9.5

Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires et les informations qui y figurent sont strictement confidentielles. Toute utilisation de ce Message non conforme à sa destination, toute diffusion ou toute publication totale ou partielle, est interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si vous avez reçu ce Message par erreur, merci de le supprimer de votre système, ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support que ce soit. Nous vous remercions également d'en avertir immédiatement l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie électronique arrivent en temps utile, sont sécurisées ou dénuées de toute erreur ou virus.
____________________________________________________

This message and any attachments (the 'Message') are intended solely for the addressees. The information contained in this Message is confidential. Any use of information contained in this Message not in accord with its purpose, any dissemination or disclosure, either whole or partial, is prohibited except formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any part of it. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or virus-free.

Hi Ros,

Using server on Cloud.

On Mon, Apr 1, 2019, 5:26 PM ROS Didier <didier.ros@edf.fr> wrote:

On 3/31/19 11:09 PM, Sathish Kumar wrote:

Hi Team,

We have a requirement to copy a table from one database server to
another database server. We are looking for a solution to achieve this
with lesser downtime on Prod. Can you help us with this?

So what is creating the downtime now?

In addition to other suggestions you might want to take a look at:

https://www.postgresql.org/docs/9.5/postgres-fdw.html

Table Size: 160GB
Postgresql Server Version: 9.5

--
Adrian Klaver
adrian.klaver@aklaver.com

As other have pointed out, you can take a pg_dump at anytime. You can
provide arguments to pg_dump to only dump a subset of the database (like
one table). Also mentioned is using a foreign data wrapper (FDW). yet
another approach is to use the "copy to/from program" command to stream the
table from one db to the other using netcat (nc):

On destination server:

\copy table_name to program "nc origin_server 9999";

On origin server:

\copy table_name from program ''nc -l 9999";

If your network is slower than your cpu you can compress the data by piping
it through lz4 (or gzip/bzip whatever, but lz4 is fast both directions for
streaming).

-Michel

On Sun, Mar 31, 2019 at 11:10 PM Sathish Kumar <satcse88@gmail.com> wrote:

Hi Adrian,
We are exporting live table data to a new database, so we need to stop our
application until the export/import is completed. We would like to minimise
this downtime.

On Mon, Apr 1, 2019, 10:22 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

/"so we need to stop our application until the export/import is completed."/

Why?

On 4/1/19 9:47 AM, Sathish Kumar wrote:

Hi Adrian,
We are exporting live table data to a new database, so we need to stop our
application until the export/import is completed. We would like to
minimise this downtime.

On Mon, Apr 1, 2019, 10:22 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 3/31/19 11:09 PM, Sathish Kumar wrote:

Hi Team,

We have a requirement to copy a table from one database server to
another database server. We are looking for a solution to achieve this
with lesser downtime on Prod. Can you help us with this?

So what is creating the downtime now?

In addition to other suggestions you might want to take a look at:

https://www.postgresql.org/docs/9.5/postgres-fdw.html

Table Size: 160GB
Postgresql Server Version: 9.5

--
Angular momentum makes the world go 'round.

On Mon, Apr 1, 2019 at 7:47 AM Sathish Kumar <satcse88@gmail.com> wrote:

Hi Adrian,
We are exporting live table data to a new database, so we need to stop our
application until the export/import is completed. We would like to minimise
this downtime.

It's more complicated if you want to keep your application running and
writing to the db while migrating. There are trigger-level replication
tools, like slony that can be used to stream changes to the new database,
and then you switch over once you get both of them to parity, but there are
some gotchas. You said the db is only 160GB, it depend a lot on what kind
of schema we're talking about, but I imagine it wouldn't take long to just
take the downtime and do a normal pg_upgrade.

The table size is 160gb. We would like to move/copy this table fro one db
server to another db server.

On Tue, Apr 2, 2019, 12:17 AM Michel Pelletier <pelletier.michel@gmail.com>
wrote:

Hi All,

Can you tell me a way for table replication or sync or to achieve minimal
downtime from dbserver1 to dbserver2 on Postgresql 9.5

Table Size: 160gb
4VCPU, 16gb RAM

On Tue, Apr 2, 2019, 12:19 AM Sathish Kumar <satcse88@gmail.com> wrote:

Hi Sathish, as Michel Pelletier, pointed out, a trigger based approach (i.e
slony-I, bucardo) or the pg_logical plugin (requires server restart) is the
way to go, personally, I've worked with slony-I, the initial setup is
somewhat tricky, but it works, depending on how transactional the table,
storage, and network speed, we're talking about seconds of sync difference
worst case scenario.

On Mon, Apr 1, 2019 at 8:23 PM Sathish Kumar <satcse88@gmail.com> wrote:

Hi All,

Can you tell me a way for table replication or sync or to achieve minimal
downtime from dbserver1 to dbserver2 on Postgresql 9.5

Table Size: 160gb
4VCPU, 16gb RAM

On Tue, Apr 2, 2019, 12:19 AM Sathish Kumar <satcse88@gmail.com> wrote:

The table size is 160gb. We would like to move/copy this table fro one db
server to another db server.

On Tue, Apr 2, 2019, 12:17 AM Michel Pelletier <
pelletier.michel@gmail.com> wrote:

On Mon, Apr 1, 2019 at 7:47 AM Sathish Kumar <satcse88@gmail.com> wrote:

Hi Adrian,
We are exporting live table data to a new database, so we need to stop
our application until the export/import is completed. We would like to
minimise this downtime.

It's more complicated if you want to keep your application running and
writing to the db while migrating. There are trigger-level replication
tools, like slony that can be used to stream changes to the new database,
and then you switch over once you get both of them to parity, but there are
some gotchas. You said the db is only 160GB, it depend a lot on what kind
of schema we're talking about, but I imagine it wouldn't take long to just
take the downtime and do a normal pg_upgrade.

On Mon, Apr 1, 2019, 10:22 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 3/31/19 11:09 PM, Sathish Kumar wrote:

Hi Team,

We have a requirement to copy a table from one database server to
another database server. We are looking for a solution to achieve

this

with lesser downtime on Prod. Can you help us with this?

So what is creating the downtime now?

In addition to other suggestions you might want to take a look at:

https://www.postgresql.org/docs/9.5/postgres-fdw.html

Table Size: 160GB
Postgresql Server Version: 9.5

--
Adrian Klaver
adrian.klaver@aklaver.com

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/