Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table Export & Import

Table Export & Import

From
Sathish Kumar
Date:
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


Re: Table Export & Import

From
Andreas Kretschmer
Date:
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

RE: Table Export & Import

From
ROS Didier
Date:

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

Tél. : +33 6 49 51 11 88

cid:image003.png@01D4BE20.1EAF68B0cid:image004.png@01D4BE20.1EAF68B0

 

 

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.

Attachment

Re: Table Export & Import

From
Sathish Kumar
Date:
Hi Ros,

Using server on Cloud.

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

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

Tél. : +33 6 49 51 11 88

cid:image003.png@01D4BE20.1EAF68B0cid:image004.png@01D4BE20.1EAF68B0

 

 

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.

Attachment

Re: Table Export & Import

From
Adrian Klaver
Date:
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

Re: Table Export & Import

From
Michel Pelletier
Date:
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 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


Re: Table Export & Import

From
Sathish Kumar
Date:
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:
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

Re: Table Export & Import

From
Ron
Date:
"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> 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.

Re: Table Export & Import

From
Michel Pelletier
Date:
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

Re: Table Export & Import

From
Sathish Kumar
Date:
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

Re: Table Export & Import

From
Sathish Kumar
Date:
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

Re: Table Export & Import

From
Rene Romero Benavides
Date:
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/