Обсуждение: Dumping data using pg_dump after chrooting to a different partition

Hi,

I am new to PostgreSQL. We are using PostgreSQL 9.0.2 on our linux server. We have an instance of PostgreSQL 9.0 running using the primary partition on the server.

We want to use the pg_dump and psql programs to migrate the data during our software upgrade process. For upgrade, we plan to do the following:

• chroot to a secondary partition on the server.
• install the software RPMs including PostgreSQL RPMs
• start a secondary instance of PostgreSQL DB server using a different port and data directory.
• run pg_dump to dump the data from the primary instance to a file.
• run psql to import the data from the file into the secondary instance.

However when I tried to perform the pg_dump as mentioned above, I get the following error:
pg_dump: [archiver (db)] connection to database "TestDB" failed: could not connect to server: No such file or directory|<LVL::Debug>
Is the server running locally and accepting|<LVL::Debug>
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?|<LVL::Debug>

pg_dump works before chrooting to the secondary partition. Can you please provide a way to dump the data after chrooting?

Thanks for your help.

Radha

Krishnamurthy Radhakrishnan <kradhak@cisco.com> wrote:

> pg_dump works before chrooting to the secondary partition. Can
> you please provide a way to dump the data after chrooting?

How about connecting through a TCP connection and piping directly
from pg_dump to psql?

Of course, these days you have the option of just using pg_upgrade
against the original or a deep copy of the data directory.

-Kevin

Hi Kevin,

Can you please elaborate how to do the following?

connecting through a TCP connection and piping directly
from pg_dump to psql

Since we could have DB schema changes between the versions, we may not be able to copy the data directory.

Does pg_upgrade support changes to the schema?

Thanks.

Radha

On 10/24/11 4:17 PM, Kevin Grittner wrote:

Krishnamurthy Radhakrishnan <kradhak@cisco.com> wrote:

pg_dump works before chrooting to the secondary partition. Can
you please provide a way to dump the data after chrooting?

 
How about connecting through a TCP connection and piping directly
from pg_dump to psql?
Of course, these days you have the option of just using pg_upgrade
against the original or a deep copy of the data directory.
-Kevin

On 10/24/11 3:10 PM, Krishnamurthy Radhakrishnan wrote:

Hi,

I am new to PostgreSQL. We are using PostgreSQL 9.0.2 on our linux server. We have an instance of PostgreSQL 9.0 running using the primary partition on the server.

We want to use the pg_dump and psql programs to migrate the data during our software upgrade process. For upgrade, we plan to do the following:

• chroot to a secondary partition on the server.
• install the software RPMs including PostgreSQL RPMs
• start a secondary instance of PostgreSQL DB server using a different port and data directory.
• run pg_dump to dump the data from the primary instance to a file.
• run psql to import the data from the file into the secondary instance.

However when I tried to perform the pg_dump as mentioned above, I get the following error:
pg_dump: [archiver (db)] connection to database "TestDB" failed: could not connect to server: No such file or directory|<LVL::Debug>
Is the server running locally and accepting|<LVL::Debug>
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?|<LVL::Debug>

I suspect the problem is that localhost sockets on Unix/Linux are actual implemented as file-system sockets rather than TCP/IP sockets.  If you do chroot, those files won't exist.  Try connecting using a "-h hostname (e.g. -h myserver.domain.com) rather than the default localhost.  You may have to reconfigure your server to listen on port 80.

Craig

Thanks Craig.

After configuring to accept TCP connections on port 5432, I tried to specify the hostname as shown below and that didn't help. Is there anything else that needs to be configured?
pg_dump -h bldr-ccm36.cisco.com -p 5432 -a -U postgres
pg_dump: [archiver (db)] connection to database "postgres" failed: could not connect to server: Connection refused
    Is the server running on host "bldr-ccm36.cisco.com" and accepting
    TCP/IP connections on port 5432?

Radha

On 10/24/11 4:45 PM, Craig James wrote:

On 10/24/11 3:10 PM, Krishnamurthy Radhakrishnan wrote:

Hi,

I am new to PostgreSQL. We are using PostgreSQL 9.0.2 on our linux server. We have an instance of PostgreSQL 9.0 running using the primary partition on the server.

We want to use the pg_dump and psql programs to migrate the data during our software upgrade process. For upgrade, we plan to do the following:

• chroot to a secondary partition on the server.
• install the software RPMs including PostgreSQL RPMs
• start a secondary instance of PostgreSQL DB server using a different port and data directory.
• run pg_dump to dump the data from the primary instance to a file.
• run psql to import the data from the file into the secondary instance.

However when I tried to perform the pg_dump as mentioned above, I get the following error:
pg_dump: [archiver (db)] connection to database "TestDB" failed: could not connect to server: No such file or directory|<LVL::Debug>
Is the server running locally and accepting|<LVL::Debug>
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?|<LVL::Debug>

I suspect the problem is that localhost sockets on Unix/Linux are actual implemented as file-system sockets rather than TCP/IP sockets.  If you do chroot, those files won't exist.  Try connecting using a "-h hostname (e.g. -h myserver.domain.com) rather than the default localhost.  You may have to reconfigure your server to listen on port 80.

Craig

On 25/10/11 11:01, Krishnamurthy Radhakrishnan wrote:
> Thanks Craig.
>
> After configuring to accept TCP connections on port 5432, I tried to
> specify the hostname as shown below and that didn't help. Is there
> anything else that needs to be configured?
> pg_dump -h bldr-ccm36.cisco.com -p 5432 -a -U postgres
> pg_dump: [archiver (db)] connection to database "postgres" failed: could
> not connect to server: Connection refused
>     Is the server running on host "bldr-ccm36.cisco.com" and accepting
>     TCP/IP connections on port 5432?

Use "localhost" or "127.0.0.1" if it's on the same machine to simplify
things. If you try to connect to your host's public IP but
postgresql.conf has listen_addresses='127.0.0.1' or
listen_addresses='localhost' then you won't be able to connect because
Pg isn't listening on your public IP, only your loopback IP. A chroot
won't affect tcp/ip, so it's still localhost when you're chrooted into
another FS.

Also, you may have firewall rules in place that prevent the connection,
check for that.

--
Craig Ringer

Thanks. Using localhost instead of the actual host FQDN helped to fix
the problem.

Thank you all for your help.

Radha

On 10/24/11 9:45 PM, Craig Ringer wrote:
> On 25/10/11 11:01, Krishnamurthy Radhakrishnan wrote:
>> Thanks Craig.
>>
>> After configuring to accept TCP connections on port 5432, I tried to
>> specify the hostname as shown below and that didn't help. Is there
>> anything else that needs to be configured?
>> pg_dump -h bldr-ccm36.cisco.com -p 5432 -a -U postgres
>> pg_dump: [archiver (db)] connection to database "postgres" failed: could
>> not connect to server: Connection refused
>>      Is the server running on host "bldr-ccm36.cisco.com" and accepting
>>      TCP/IP connections on port 5432?
> Use "localhost" or "127.0.0.1" if it's on the same machine to simplify
> things. If you try to connect to your host's public IP but
> postgresql.conf has listen_addresses='127.0.0.1' or
> listen_addresses='localhost' then you won't be able to connect because
> Pg isn't listening on your public IP, only your loopback IP. A chroot
> won't affect tcp/ip, so it's still localhost when you're chrooted into
> another FS.
>
> Also, you may have firewall rules in place that prevent the connection,
> check for that.
>
> --
> Craig Ringer