Re: Question about loading up a table

From: Alex Samad <alex(at)samad(dot)com(dot)au>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: vinny <vinny(at)xs4all(dot)nl>, PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: Re: Question about loading up a table
Date: 2017-08-01 06:16:29
Message-ID: CAJ+Q1PUKVfOpPKsiMOCwJj0me=XGd2ZO4x0q7+h-BUv-fC_iYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I double checked and there is data going over, thought I would correct that.

But it seems to be very slow. Having said that how do I / what tools do I
use to check through put

A

On 1 August 2017 at 08:56, Alex Samad <alex(at)samad(dot)com(dot)au> wrote:

> Hi
>
> I'm using pg_dump 9.6 to do the dumps.
>
> I'm also pretty sure no data is being replicated until the end of the copy
> stdin as I was watching tcpdump output and I can see data from the orig
> master to the new master and no traffic between new master and the standby,
> pretty sure my replication is working as my other tables have replicated
> over.
>
>
> as for allow pg_dump to create copy stdin with specific number of rows -
> not sure what that is so hard / bad, if it was a option for somebody to
> use. For my situation its sounds like a really good idea.
>
> I'm dumping a single table into a new single table so constraint shouldn't
> be a problem.
>
>
> Guess I have to just let it rung to completion
>
> Thanks
>
>
>
> On 1 August 2017 at 06:59, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
>> On Mon, Jul 31, 2017 at 2:31 AM, vinny <vinny(at)xs4all(dot)nl> wrote:
>> > On 2017-07-31 11:02, Alex Samad wrote:
>> >>
>> >> Hi
>> >>
>> >> I am using pg_dump | psql to transfer data from my old 9.2 psql into a
>> >> 9.6 psql.
>>
>> Note that you should be doing pg_dump with 9.6's pg_dump, as it's
>> possible for 9.2's pg_dump to not know about a 9.6 feature.
>>
>> >> The new DB server is setup as master replicating to a hot standby
>> >> server.
>> >>
>> >> What I have noticed is that the rows don't get replicated over until
>> >> the copy from stdin is finished... hard to test when you have M+ lines
>> >> of rows.
>>
>> SNIP
>>
>> >> Is there a way to tell the master to replicate earlier
>> >
>> > I highly doubt it, because the master cannot know what to replicate
>> until
>> > your transaction is ended with a COMMIT. If you end with ROLLBACK,
>> > or your last query is DELETE FROM (your_table>;
>> > then there isn't even anything to replicate at all...
>>
>> This is actually a visibility issue. All the new changes are
>> replicated to the slave, but just like on the master, other
>> connections can't see the change because it's not visible. The slave,
>> except for some small delay (seconds etc) is an exact replica of the
>> master. So even a delete at the end gets replicated. You just don't
>> see anything but possible table bloat to show for it.
>>
>> To prove this to oneself, start the copy, and get into another session
>> to the master. You don't see any rows there either until the commit
>> after the copy.
>>
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Murtuza Zabuawala 2017-08-01 07:21:02 Not able to create collation on Windows
Previous Message Alex Samad 2017-07-31 22:58:08 Re: partitioning question