pg_dumpall: does not exist database

Ari Kahn <akahn1@gmu.edu> writes:

I was trying to dump all my databases:
su - postgres
/usr/local/bin/pg_dumpall > /Volumes/Space/postgresql_060425.dump

I get:
" does not exist database "foodmartto database "foodmart
", exiting: pg_dump failed on database "foodmart

I guess I had a database called foodmart at one time. However, it
doesn't show up in the DB list:
postgres=# \l
List of databases
Name | Owner | Encoding
---------------+----------+-----------
barry | barry | SQL_ASCII
| kahn | SQL_ASCII
.

Are you trying to accurately reproduce the formatting of what you see?
If so, I'm wondering if you've got a database with a carriage return
embedded in the name, or something like that.

What PG version is this?

regards, tom lane

On Apr 25, 2006, at 3:25 AM, Tom Lane wrote:

Ari Kahn <akahn1@gmu.edu> writes:

I was trying to dump all my databases:
su - postgres
/usr/local/bin/pg_dumpall > /Volumes/Space/postgresql_060425.dump

I get:
" does not exist database "foodmartto database "foodmart
", exiting: pg_dump failed on database "foodmart

I guess I had a database called foodmart at one time. However, it
doesn't show up in the DB list:
postgres=# \l
List of databases
Name | Owner | Encoding
---------------+----------+-----------
barry | barry | SQL_ASCII
| kahn | SQL_ASCII
.

Are you trying to accurately reproduce the formatting of what you see?

Yes

If so, I'm wondering if you've got a database with a carriage return
embedded in the name, or something like that.

That was a good idea. But this is not the case.
postgres=# drop database "\n";
ERROR: database "\n" does not exist
postgres=# drop database "\r";
ERROR: database "\r" does not exist

I think the issue is something like this though.

What PG version is this?

8.1

Ari

Ari Kahn <akahn1@gmu.edu> writes:

On Apr 25, 2006, at 3:25 AM, Tom Lane wrote:

If so, I'm wondering if you've got a database with a carriage return
embedded in the name, or something like that.

That was a good idea. But this is not the case.
postgres=# drop database "\n";
ERROR: database "\n" does not exist
postgres=# drop database "\r";
ERROR: database "\r" does not exist

Those tests have little to do with what I'm worried about. Backslash
isn't an escape character in SQL names, and even if it were, your tests
only checked for databases named exactly "one newline" or "one carriage
return", not for names comprising those characters along with others.

regards, tom lane

Ari Kahn wrote:

I think the issue is something like this though.

Send the output of your database listing to a pipe through 'cat -evt'
and see if you've got any unusual characters in the names of your databases:

echo '\l' | psql template1 |cat -evt

--
Until later, Geoffrey

Any society that would give up a little liberty to gain a little
security will deserve neither and lose both. - Benjamin Franklin

* Ari Kahn (akahn1@gmu.edu) wrote:

That was a good idea. But this is not the case.

You might try just looking at pg_database directly:

select * from pg_database;

Or (as someone else suggested) pipeing the output into a file which
you can then look at.

As a side-note: I'm a graduate student at GMU and will be at the main
Fairfax campus this afternoon (probably starting around 3pm) and I've
got classes there tonight (4:30pm and 7:20pm). I'd be happy to help
anyone at GMU with Postgres. :)

Thanks,

Stephen

On Apr 25, 2006, at 8:46 AM, Stephen Frost wrote:

* Ari Kahn (akahn1@gmu.edu) wrote:

That was a good idea. But this is not the case.

You might try just looking at pg_database directly:

select * from pg_database;

Or (as someone else suggested) pipeing the output into a file which
you can then look at.

That was a good idea. At least I could see the name of the DB:

     datname    | datdba | encoding | datistemplate | datallowconn |  
datconnlimit | datlastsysoid | datvacuumxid | datfrozenxid |  
dattablespace | datconfig |         datacl
---------------+--------+----------+---------------+-------------- 
+--------------+---------------+--------------+-------------- 
+---------------+-----------+------------------------
postgres      |     10 |        0 | f             | t             
|           -1 |         10791 |          499 |          499  
|          1663 |           |
barry         |  16387 |        0 | f             | t             
|           -1 |         10791 |          575 |          575  
|          1663 |           |
foodmart
      |  16384 |        0 | f             | t            |            
-1 |         10791 |          576 |          576 |          1663  
|           |

You'll notice the database foodmart has a carriage return or new
line. I still can't figure out how to get rid of it though.
The other weird thing is that the database name does not appear
during the psql query. It only appears when I pipe it out.

Ari Kahn <akahn1@gmu.edu> writes:

You'll notice the database foodmart has a carriage return or new
line. I still can't figure out how to get rid of it though.

Perhaps something along the lines of

drop database "foodmart
";

regards, tom lane

On Apr 25, 2006, at 10:51 AM, Tom Lane wrote:

Ari Kahn <akahn1@gmu.edu> writes:

You'll notice the database foodmart has a carriage return or new
line. I still can't figure out how to get rid of it though.

Perhaps something along the lines of

drop database "foodmart
";

regards, tom lane

I tried that. Doesn't work.
Using "od -a" I did determine that there is a CR (carriage return) in
the name.

0001240 sp sp sp | sp nl sp f o o d m a r t cr

why not just

update pg_database set datname='foodmart' where datname like 'foodmart%';

---------- Original Message -----------
From: Ari Kahn <akahn1@gmu.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephen Frost <sfrost@snowman.net>, pgsql-general@postgresql.org
Sent: Tue, 25 Apr 2006 11:08:09 -0400
Subject: Re: [GENERAL] pg_dumpall: does not exist database

On Apr 25, 2006, at 10:51 AM, Tom Lane wrote:

Ari Kahn <akahn1@gmu.edu> writes:

You'll notice the database foodmart has a carriage return or new
line. I still can't figure out how to get rid of it though.

Perhaps something along the lines of

drop database "foodmart
";

regards, tom lane

I tried that. Doesn't work.
Using "od -a" I did determine that there is a CR (carriage return) in
the name.

0001240 sp sp sp | sp nl sp f o o d m a r t cr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------- End of Original Message -------

I would call you an genius, but ... :-)

Anyway, that worked and is the solution!
postgres=# update pg_database set datname='foodmart' where datname
like 'foodmart%';
UPDATE 1
postgres=# drop database foodmart;
DROP DATABASE

THANKS!

On Apr 25, 2006, at 11:13 AM, Jim Buttafuoco wrote:

just for the record the following also works

from the psql prompt:
jim=# create database "testing
jim"# ";
CREATE DATABASE
jim=# drop database "testing
jim"# "
jim-# ;
DROP DATABASE
jim=#

and from the unix shell:
createdb "testing
"
dropdb "testing
"

you need the double quotes in all cases

---------- Original Message -----------
From: Ari Kahn <akahn1@gmu.edu>
To: jim@contactbda.com
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Stephen Frost <sfrost@snowman.net>, pgsql-general@postgresql.org
Sent: Tue, 25 Apr 2006 11:19:57 -0400
Subject: Re: [GENERAL] pg_dumpall: does not exist database

I would call you an genius, but ... :-)

Anyway, that worked and is the solution!
postgres=# update pg_database set datname='foodmart' where datname
like 'foodmart%';
UPDATE 1
postgres=# drop database foodmart;
DROP DATABASE

THANKS!

On Apr 25, 2006, at 11:13 AM, Jim Buttafuoco wrote:

why not just

update pg_database set datname='foodmart' where datname like
'foodmart%';

---------- Original Message -----------
From: Ari Kahn <akahn1@gmu.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephen Frost <sfrost@snowman.net>, pgsql-general@postgresql.org
Sent: Tue, 25 Apr 2006 11:08:09 -0400
Subject: Re: [GENERAL] pg_dumpall: does not exist database

On Apr 25, 2006, at 10:51 AM, Tom Lane wrote:

Ari Kahn <akahn1@gmu.edu> writes:

You'll notice the database foodmart has a carriage return or new
line. I still can't figure out how to get rid of it though.

Perhaps something along the lines of

drop database "foodmart
";

regards, tom lane

I tried that. Doesn't work.
Using "od -a" I did determine that there is a CR (carriage return) in
the name.

0001240 sp sp sp | sp nl sp f o o d m a r
t cr

---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------- End of Original Message -------

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------- End of Original Message -------

On Tue, 2006-04-25 at 17:49, Jim Buttafuoco wrote:

just for the record the following also works

from the psql prompt:
jim=# create database "testing
jim"# ";
CREATE DATABASE
jim=# drop database "testing
jim"# "
jim-# ;
DROP DATABASE
jim=#

and from the unix shell:
createdb "testing
"
dropdb "testing
"

you need the double quotes in all cases

I'm afraid the OP had a CR and from a unix shell you'll get a LF for the
new line. So for him it did not work. The trick with the wildcard is
very useful in other similar situations too ;-)

Cheers,
Csaba.