Thread: Getting back names of databases
I know that I've seen this somewhere in past messages, but I can't seem to find how to search the archives. Is the search engine offline? Anyway, I'm trying to write a shell script which will backup my databases to DAT tape. I'd like to know how I could get the list of existing database names from Postgres. For example, I have 3 databases named db01, db02, db03. How can I ask Postgres for these 3 names? Thanks. -Tony
> I know that I've seen this somewhere in past messages, but I can't seem > to find how to search the archives. Is the search engine offline? > > Anyway, I'm trying to write a shell script which will backup my > databases to DAT tape. I'd like to know how I could get the list of > existing database names from Postgres. For example, I have 3 databases > named db01, db02, db03. How can I ask Postgres for these 3 names? There are a few ways... such as doing something like "psql -d template1 -c \l" on the command line, and parsing the output. You could also do a pg_dumpall instead of a pg_dump, but it's not always the best for a backup. It makes it nice if you have to restore the entire server, but if you just need to restore one particular database, then you have to edit the file. steve
"G. Anthony Reina" wrote: > > I know that I've seen this somewhere in past messages, but I can't seem > to find how to search the archives. Is the search engine offline? > > Anyway, I'm trying to write a shell script which will backup my > databases to DAT tape. I'd like to know how I could get the list of > existing database names from Postgres. For example, I have 3 databases > named db01, db02, db03. How can I ask Postgres for these 3 names? > > Thanks. > -Tony from psql, you can do a \l (or from a command line psql -c "\l") or you can query the system tables yourself: select datname from pg_database there's a lot of ways to skin that cat, it's just a matter of what works best for you jeff
Steve Wolfe wrote: > > > I know that I've seen this somewhere in past messages, but I can't seem > > to find how to search the archives. Is the search engine offline? > > [cut] > > steve Sorry for the OT posting, but did anyone notice anything weird about the original posting (Reina's)? Netscape Messenger gets an error for me when I tried to get my mail this morning. Gave me fits to figure out what it was choking on; seemed to be that one. Don't know if it's my client or my ISP that's choking; so far I don't see anything especially strange about the message itself. Anyone else had problems with that message?
You could run the following statement from a shell. It returns just the name of all database, one name per line. The "-A -q -t" turns off all formatting and other superfluous messages. I'm assume you wouldn't really need to back up template1 at the same time as your other databases, so it is purposely excluded. psql -A -q -t -d template1 -c "select datname from pg_database where datname <> 'template1'" Phil -----Original Message----- From: Steve Wolfe [mailto:steve@iboats.com] Sent: Monday, May 22, 2000 11:10 AM To: pgsql-general@hub.org Subject: Re: [GENERAL] Getting back names of databases > I know that I've seen this somewhere in past messages, but I can't seem > to find how to search the archives. Is the search engine offline? > > Anyway, I'm trying to write a shell script which will backup my > databases to DAT tape. I'd like to know how I could get the list of > existing database names from Postgres. For example, I have 3 databases > named db01, db02, db03. How can I ask Postgres for these 3 names? There are a few ways... such as doing something like "psql -d template1 -c \l" on the command line, and parsing the output. You could also do a pg_dumpall instead of a pg_dump, but it's not always the best for a backup. It makes it nice if you have to restore the entire server, but if you just need to restore one particular database, then you have to edit the file. steve