0

I dont have a lot of experience with Postgres, so I'm having a bit of a trouble accessing a database I just rescued from a broken Ubuntu server.

What I'm trying to do: The server that was sporting postgres is dead now, I can only access it via "rescue mod" provided by the hosting company. I tried chroot in order to dump the database using pg_dump or pg_dumpall, but it seems that the server is unreachable in this way. The dump attempts to enroute himself via rescue.ovh.net (OVH being the hosting provider), even if I specify -h localhost.

So I came with a different idea: copying the whole Postgres folder into a local machine, and then dumping the database and keep on restoring everything starting from this dump. This is something you can do in MySQL, so I thought that this maybe would be possible with Postgres.

But so far it is not workin. I copied /var/lib/postgres folder to my local machine (taking care that the owner in my local machine is also the postgres user), but when I try to dump a database, I can't really do it.

The errors vary on the command:

My first attempt is to dump the database using the database user:

$ pg_dump -U discourse -h localhost > discourse_prod.sql
Password: 
pg_dump: [archiver (db)] connection to database "discourse" failed: fe_sendauth: no password supplied

The prompt asks for a password, the user did not have a password, but If i just press enter it aches and says no password provided.

My second attempt was dumping via postgres admin user:

sudo pg_dump -U postgres discourse_prod > ~/test.sql

I get a pg_dump: [archiver (db)] connection to database "discourse_prod" failed: FATAL: Peer authentication failed for user "postgres". So I try to switch user before trying to dump...

sudo -u postgres pg_dump -Fp discourse_prod > dump.sql

and now it seems that the database was not properly copied: pg_dump: [archiver (db)] connection to database "discourse_prod" failed: FATAL: database "discourse_prod" does not exist

As I said, I have not very much experience with Postgres, and I'm running out of ideas... on how to get a dump out from these files, i don't mind if I manage to get it from the devastated machine or from my locally copied files.

Any ideas?

versvs
  • 643
  • 2
  • 12
  • 30
  • First try `sudo -u postgres psql -l` to get the names of the databases that are present in this installation. – wildplasser May 31 '14 at 10:59
  • Thanks @wildplasser, definitely my copied files are not being loaded, as the database I'm looking for is not listed. In the server running under rescue mod I find the same "unreacheable" error as it tries to pass the petition through another machine. – versvs May 31 '14 at 11:05
  • Sorry given the lack of information and your lack of basic postgres-skills it seems impossible to answer your question. Maybe you should first practice on a toy-installation (if you have an extra machine available) ? – wildplasser May 31 '14 at 11:37
  • What information may I provide? What would be the starting point for a procedure aimed at get my files (copied with the postgres server shutdown, this point is OK) up and running? Thanks @wildplasser – versvs May 31 '14 at 12:56
  • You could use `pg_dumpall` to dump *all* the databases in the installation , or first use `psql -l` to get a list of present databases and use `pg_dump` as in your latest attempt. Dont forget to change directory (e.g. to /tmp/ ) because the postgres user may not be allowed to write the dumpfile(s) into your current directory. Good luck! – wildplasser May 31 '14 at 13:20
  • The problem right now is that I either have a blank postgresql server (the one in my local machine) with only 3 databases created during installation or that I don't have a server at all, well... at least not a server I can reach (I get `Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?` when I try to dump a db after shutting down postgresql server, replacing files, and rebooting (which I understand might be enough to restart the service of psql). The `pg_hba.conf` specifies localhost (default) listening, and port 5432 as open. – versvs May 31 '14 at 14:43
  • Google that error message. (OSX ?) – wildplasser May 31 '14 at 14:49
  • Ubuntu, I googled this morning (I touched that hehe). Right now I think I managed to get a proper dump of the database from the broken server, I still need to try restoring, but this may be definitely a milestone... There is a lot more help regarding regular dump/restoring than this emergency case I am (still) facing. – versvs May 31 '14 at 14:53
  • 1
    Firts fix your connection/installation problems : http://stackoverflow.com/questions/8465508/can-not-connect-to-local-postgresql – wildplasser May 31 '14 at 19:02

0 Answers0