Skip site navigation (1) Skip section navigation (2)

Re: Copy database to another machine

From: Nuno Ferreira <nunoadferreira(at)gmail(dot)com>
To: jjurban <jjurban(at)attglobal(dot)net>
Cc: Thom Brown <thom(at)linux(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Copy database to another machine
Date: 2011-10-27 08:04:55
Message-ID: CAO65WWv=vx_QRw7NFrNKsjqZ8MbX+SPSMLx20iUueSww9fCxPg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Imagine I have 999 entries in the table in one machine and 1000 in the other
machine. There are any easy way of copy only the data that doesn't exist?

2011/10/26 jjurban <jjurban(at)attglobal(dot)net>

> Thom Brown wrote:
>
>> On 26 October 2011 17:54, jjurban <jjurban(at)attglobal(dot)net> wrote:
>>
>>
>>> Thom Brown wrote:
>>>
>>>
>>>> On 26 October 2011 14:38, Nuno Ferreira <nunoadferreira(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>
>>>>
>>>>> Good afternoon,
>>>>> I'm trying to copy one database from one machine to another. How can I
>>>>> do
>>>>> it?
>>>>> Can I just simply copy and paste the files? I need to close the
>>>>> database
>>>>> first or I can copy the files with it open?
>>>>>
>>>>>
>>>>>
>>>> No, you can't copy and paste the files, but you can do it in one step:
>>>>
>>>> pg_dump -t table_to_dump source_database_name | psql
>>>> destination_database_name
>>>>
>>>> Assuming the table does't already exist in the destination, it will
>>>> create and populate the table in the destination database.
>>>>
>>>>
>>>>
>>> I have zipped the contents of PGSDATA (the entire database)  to a zip
>>> file,
>>> copied that file to a memory stick, then unzipped it to another machine.
>>>
>>> This is on eCS (OS/2) machines.
>>>
>>> I have done this many times without problems.
>>>
>>> Am I doing something I shouldn't?
>>>
>>>
>>
>> That's copying an entire cluster.  You can't copy individual databases
>> or tables in this manner.  If you are copying your cluster at the
>> filesystem level (either by zipping, tar'ing or whatever). you would
>> also either need to shut down the
>> database whilst making such a copy, or use pg_start_backup('label')
>> and pg_stop_backup() with WAL archiving, or use pg_basebackup in order
>> to avoid risking data inconsistency.
>>
>>
>>
> Yes, I always shut down the database server with a call to
>
> PG_CTL  stop -m smart
>
> before zipping.
>
> John
>
>
>


-- 
Com os Melhores Cumprimentos
Nuno Ferreira
914663795

In response to

pgsql-novice by date

Next:From: pg noobDate: 2011-10-28 14:30:13
Subject: best way to do bulk delete?
Previous:From: Thom BrownDate: 2011-10-26 19:59:04
Subject: Re: Copy database to another machine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group