WebFaction
Community site: login faq

I have written into a fabfile a task to restore a dump file into a PostgreSQL database. Since we cannot dropdb or createdb on the machines, I have to use the API to delete and recreate the database. However, I have found the steps for this are a little awkward. You must:

  1. delete_db
  2. delete_db_user
  3. create_db

delete_db does not delete the user (sensibly), yet create_db does not allow you to specify a user. (You can specify one when creating from the control panel.) Rather it creates one, which is always given the same name as the database, and it throws an error if that user already exists.

My question is, is this the best (or maybe only) way to delete and create a database of the same name from the API? Or am I missing some way to specify a user when calling create_db?

asked 01 Feb '13, 16:04

benspaulding
1313
accept rate: 0%


At the moment, the way you're doing it is the only way possible. I'll have our developers look into updating the API to include an optional user parameter for create_db.

permanent link

answered 01 Feb '13, 16:18

seanf
12.2k41836
accept rate: 37%

Thank you for the affirmative answer regarding the API. Having a user parameter for create_db would be great.

But related to this question, I just learned that I may not need to drop and recreate the database. I am not yet entirely sure what all pg_restore’s --clean option does, but using it allows me to restore into the database without errors, and without having to destroy/recreate.

The docs say its purpose is to “Clean (drop) database objects before recreating them.” But I don’t know if I will end up with a dirty database if objects are in the database that are not in the dump. I assume those will not be dropped. If anyone has any insight, I would appreciate it.

(01 Feb '13, 17:29) benspaulding

Unfortunately --clean doesn't do what you want. It will drop any database tables you restore, but any other database tables that are not in the database dump won't be deleted. You can test this with the following:

psql -U myuser -d mydb

create table test_41 (id integer,name varchar(32));
insert into test_41 values (1,'a'),(2,'b'),(3,'c');
select * from test_41;
(Ctrl-d to exit psql)

pg_dump -Fc -U myuser -f myuser.dump

psql -U myuser -d mydb

delete from test_41 where id=3;
select * From test_41;

create table test_42 (id integer,name varchar(32));
insert into test_42 values (1,'A'),(2,'B'),(3,'C');
select * from test_42;
(Ctrl-d to exit psql)

pg_restore -U myuser -d mydb -c myuser.dump

You'll see that the test_41 table is correctly dropped and restored, so it has its (a, b, c) dataset back correctly, but the test_42 table still exists with all its data (A, B, C).

Do you need to delete the database user each time? It seems you could drop just the database, leaving the user intact, and then running pg_restore using the -C (create database) option to create an empty database under that user and import the data.

I grabbed this example off the discussion here. The author doesn't mention the downfalls of --clean, but he does state that there are certain cases when it's more convenient than other methods.

(01 Feb '13, 19:43) ryans ♦♦

Thanks for the good info on --clean. Good to know that is not quite what I want.

Do you need to delete the database user each time? It seems you could drop just the database, leaving the user intact, and then running pg_restore using the -C (create database) option to create an empty database under that user and import the data.

Yes, I believe I need to. Like I mentioned, as far as I can see, databases cannot be dropped or created from the command line. It has to be done in either the control panel or with the API.

$ dropdb -U mydbuser mydb
dropdb: could not connect to database postgres: FATAL:  no pg_hba.conf entry for host "[local]", user "mydbuser", database "postgres", SSL off

$ createdb -U mydbuser mynewdb
createdb: could not connect to database postgres: FATAL:  no pg_hba.conf entry for host "[local]", user "mydbuser", database "postgres", SSL off

$ pg_restore -U mydbuser -COxFc -n public -d myotherdb tmp/mydb.pgdump 
# Note, there are no errors, as if it worked.
$ psql -U mydbuser mydb
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "mydbuser", database "mydb", SSL off
# But the db is not there, nor in the control panel, nor can the API find it.

I tried deleting just the database with the API, and still cannot create a db using the existing user.

(02 Feb '13, 10:55) benspaulding
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×84
×75

question asked: 01 Feb '13, 16:04

question was seen: 2,580 times

last updated: 02 Feb '13, 10:55

WEBFACTION
REACH US
SUPPORT
AFFILIATE PROGRAM
LEGAL
© COPYRIGHT 2003-2019 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM