Setting up postgres on VPS for production

Question

So, I have a Pyramid app with a postgres database on my local machine. I did a pg_dump to get a dump of the data in my database, called pg_dump_2014-04-22. I then git pushed this file, and did a git pull in the VPS to get the file.

Now, I have already installed postgres on my VPS. When I sudo -u postgres psql on my VPS, I can connect to it but there are no relations (naturally).

Both my username and database name are postgres.

So, I tried psql postgres < pg_dump_2014-04-22, but this gives the error psql: FATAL: role "root" does not exist.

I also tried pg_restore -h localhost -U postgres -d postgres pg_dump_2014-04-22, and that prompts me for my password, but then throws the error pg_restore: [archiver(db)] connection to database "postgres" falied: FATAL: password authentication failed for user postgres"

What am I missing here?

(temporally) add a line local postgres postgres trust to your pg_hba.conf file. — joop
– joop, Commented Apr 22, 2014 at 12:26

Fabrizio Mazzoni · Accepted Answer · 2014-04-22 12:27:09Z

1

You first have to create a user and the database where you want to import your dump

su postgres

createuser root

createdb yourdb

Then import the dump with

psql -d yourdb -f pg_dump_2014-04-22

answered Apr 22, 2014 at 12:27

Fabrizio Mazzoni

1,9303 gold badges25 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user330315 Over a year ago

There is no need to create a root user in Postgres.

Fabrizio Mazzoni Over a year ago

True but since he is trying to import ad root that's why I have included the user creation

user330315 Over a year ago

It would be much better to simply provide the correct postgres username to psql using the -U option.

bard Over a year ago

Thanks for the tips, I have followed these steps but am getting an UTF-8 encoding error. (I think, actually it messed up the terminal's output in the VPS so I can't read it properly). Would something like this work? createdb mydb -E utf-8

Fabrizio Mazzoni Over a year ago

Give it a go. Since you're importing you can still drop and recreate the db

|

Collectives™ on Stack Overflow

Setting up postgres on VPS for production

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related