Full postgres backup

sir-lancealot · 04-19-2014, 07:44 AM

ok, I am a MySQL guy (really 1st time touching postgres) but doing some work to help someone and there moving to Amazon RDS. The server has around 110 databases, so I did a pg_dumpall and have my file (2.2G) and doing an import.

So here is the problem. It stars by prompting for the postgres PW, I enter and then it seems to create all the tables, then prompts again. I enter it and it looks like it restores all the data for that database, then prompts again, restores, and on and on. I would like to just issue the restore and walk away for the restore.

I created a .pgpass file but the problem I see with that doc is shows this as the "what to put in that file";
hostname : port : database : username : password

So do I need to copy/paste that 100+ times changing the database name for each or is there another way?

I am issuing the following command;
psql -f full.sql --host=rdsURL --port=5432 --username=postgres --password
(and I tried without the password prompt)

Thanks for all read/replies

jlinkels · 04-19-2014, 09:16 AM

I know where you are. I am a mysql person as well, and administrating postgres drives me nuts. I am sure it has its merits but I haven't discovered them yet.

You have to change some defaults in the /etc/postgresql/pg_hba file. I don´t exactly recall what they were, but mine look likes this now:

Code:

# Database administrative login by UNIX sockets
local   all         postgres                                        ident sameuser
#
# All other connections by UNIX sockets
local   all         all                                             ident sameuser
#
# All IPv4 connections from localhost
host    all         all         127.0.0.1         255.255.255.255   ident sameuser
#

And my .pgpass file looks like this:

Code:

server.domain.com:5432:*:admin:mypass

I think the secret is in the '*' for the database name.

I am not sure how large a security hole I created with altering the pg_hba. Be sure to plug it back once you are done. Or try to understand what you are doing

Oh, and be extremely careful restoring databases. If you forget to specify which database has to be restored, you are restoring into the template0 database or so. Very disturbing. When you make a database dump (there are 3 or 4 different methods to do so) the database database name is not included by default either.

jlinkels

sir-lancealot · 04-19-2014, 10:34 PM

Thanks, I guess I was thinking out loud, a database is a database, a few monitor differences, but yes, I am looking for the benefit!

Well, I can't touch the hba conf as this is being move to amazon's RDS and not a server, BUT, looking at your second change (* for the database), I made that change, issued the same restore command and next thing I know, it's flying by never asking!

So, that really did solve it. Now as for the temlate0, etc. I have no clue, but there trying to get a postgres DBA part time, so I have plenty of other tasks ahead of me, this was get we get the data over, so this now shows I can (pgadmin login shows all my data).

I will move on to other tasks, and as the staging area get's set, I will let him confirm things are correct, but what a time saver.

Thanks much .. have a great night!