Thread: pg_dump help
I am trying to copy a table from my local database to a client's. The problem is I keep getting a message from pg_dump: "can't find template1 database. You are really hosed." The docs say that this usually means the postmaster is not running but it is. In fact, I can get into the database and do whatever I want to with it. Over the weekend I did try and install the 7.0.2 RPMS without much luck. I uninstalled them and reinstalled 6.5.1-3. I then reloaded my databases. This is the first time I've done a pg_dump since. pg_dumpall gives me the same error. If you've got any advice on how I can save these two tables I need so I can get them to my customer I'd appreciate it. Redhat 6.0 OS len morgan
Len Morgan wrote:
>
> I am trying to copy a table from my local database to a client's. The
> problem is I keep getting a message from pg_dump:
>
> "can't find template1 database. You are really hosed."
>
> The docs say that this usually means the postmaster is not running but it
> is. In fact, I can get into the database and do whatever I want to with it.
> Over the weekend I did try and install the 7.0.2 RPMS without much luck. I
> uninstalled them and reinstalled 6.5.1-3. I then reloaded my databases.
> This is the first time I've done a pg_dump since. pg_dumpall gives me the
> same error.
>
> If you've got any advice on how I can save these two tables I need so I can
> get them to my customer I'd appreciate it.
>
> Redhat 6.0 OS
>
> len morgan
In 6.x, pg_database sometimes became out of sync. Perform a:
SELECT oid, * FROM pg_database;
That error is usually a symptom of either a missing entry for
template1 or a duplicate entry. If it is missing, you'll have to
add the record yourself with an insert:
INSERT INTO pg_database VALUES ('template1', 100, 0,
'template1');
If there are duplicates, delete the duplicate:
DELETE FROM pg_database WHERE oid = X;
where 'X' is the oid of the duplicate.
Hope that helps,
Mike Mascari
"Len Morgan" <len-morgan@crcom.net> writes:
> I am trying to copy a table from my local database to a client's. The
> problem is I keep getting a message from pg_dump:
> "can't find template1 database. You are really hosed."
Wow --- that does fall in the category of "shouldn't happen" errors.
(I assume you didn't do anything as silly as DROP DATABASE template1,
right?) The code that's coming out of is in src/bin/pg_dump/pg_dump.c:
res = PQexec(g_conn,
"SELECT oid from pg_database where datname = 'template1'");
if (res == NULL ||
PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "pg_dump error in finding the template1 database. Explanation from backend: '%s'.\n",
PQerrorMessage(g_conn));
exit_nicely(g_conn);
}
ntups = PQntuples(res);
if (ntups != 1)
{
fprintf(stderr, "pg_dump: couldn't find the template1 database. "
"You are really hosed.\nGiving up.\n");
exit_nicely(g_conn);
}
and offhand I don't see how that could go wrong short of truly-spectacular
failures. What do you see if you execute "SELECT oid,datname from
pg_database" by hand? What shows up in the postmaster log?
regards, tom lane
>I am trying to copy a table from my local database to a client's. The >problem is I keep getting a message from pg_dump: > >"can't find template1 database. You are really hosed." If this isn't fixed yet.....Can I get a screenshot of that? We have a hall of shame here, ya know ;) Rob Nelson rdnelson@co.centre.pa.us
At 08:18 8/08/00 -0400, Robert D. Nelson wrote:
>>I am trying to copy a table from my local database to a client's. The
>>problem is I keep getting a message from pg_dump:
>>
>>"can't find template1 database. You are really hosed."
>
>If this isn't fixed yet.....Can I get a screenshot of that? We have a hall
>of shame here, ya know ;)
Are you suggesting that if an open source project has not fixed a bug in
24-48 hours, it should go into a hall of shame?
Or do you just like the error message?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
>Are you suggesting that if an open source project has not fixed a bug in >24-48 hours, it should go into a hall of shame? > >Or do you just like the error message? Just the error message...Oh yeah, it's a beaut ;) Rob Nelson ronelson@vt.edu Rob Nelson rdnelson@co.centre.pa.us
-----Original Message----- From: Robert D. Nelson <RDNELSON@co.centre.pa.us> >>I am trying to copy a table from my local database to a client's. The >>problem is I keep getting a message from pg_dump: >> >>"can't find template1 database. You are really hosed." > >If this isn't fixed yet.....Can I get a screenshot of that? We have a hall >of shame here, ya know ;) > > >Rob Nelson >rdnelson@co.centre.pa.us > > Sorry, I did "fix it" although I can recreate it if you want to see it. I still seem to have an additional problem though that is somewhat related: All of my tables, indexes, and sequences are doubled if you do a \dt or \di or \ds! If I do pg_dump now, I get two CREATE TABLEs, two COPYs, two CREATE INDEXs etc. If I dump just one table, then fix the file manually, then drop the table and reload it, I end up with 2 tables in the database again! If I go look in the PGDATA directory, I only have one copy of each table there. I'm afraid that I'm going to have to dump ALL of my tables in ALL of my databases, manually fix them (250MB+ !) and then remove every bit of evidence that I ever had Postgres on this machine and then try and reload. I suspect that some of my problem came from trying to upgrade to 7.0 and not COMPLETELY eliminating the old pgdata directory (the new RPM puts it one level lower in the directory tree). I'm sure the README.rpm tells me that I must do this or grave things might happen. I guess that's why you should always read the documentation BEFORE you do something. :-( len morgan
Is there a date set, or at can anyone at least give us a hint? André Næss
On Tue, 8 Aug 2000, [iso-8859-1] Andr� N�ss wrote: > Is there a date set, or at can anyone at least give us a hint? End of October, or there abouts ...
On Tue, Aug 08, 2000 at 08:20:16AM -0500, Len Morgan wrote: > -----Original Message----- > From: Robert D. Nelson <RDNELSON@co.centre.pa.us> > > >>I am trying to copy a table from my local database to a client's. The > >>problem is I keep getting a message from pg_dump: > >> > >>"can't find template1 database. You are really hosed." > > > > I still seem to have an additional problem though that is somewhat related: > > All of my tables, indexes, and sequences are doubled if you do a \dt or \di > or \ds! > Hmm, this sounds like an old problem with the pg_user table getting corrupt. try: SELECT * FROM pg_user. Do you see only one user named postgres? ISTR that this could even cause the 'can't find template1 database' error. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005