[ADMIN] Dumping data using pg_dump after chrooting to a different partition

[Krishnamurthy Radhakrishnan]

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

[Kevin Grittner]

Post by Krishnamurthy Radhakrishnan
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

[Krishnamurthy Radhakrishnan]

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

Post by Kevin Grittner

Post by Krishnamurthy Radhakrishnan
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

[Craig James]

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.
* 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.
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

[Krishnamurthy Radhakrishnan]

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

Post by Craig James

Post by Krishnamurthy Radhakrishnan
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
* 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
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

[Craig Ringer]

Post by Krishnamurthy Radhakrishnan
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

[Krishnamurthy Radhakrishnan]

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

Thank you all for your help.

Radha

Post by Craig Ringer

Post by Krishnamurthy Radhakrishnan
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