Обсуждение: pg_dumpall: does not exist database
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
.
.
.
There is that one nagging line though with no DB name:
| kahn | SQL_ASCII
How do I drop this no-name DB?
Thanks,
Ari
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: > > 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 ------- >
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.