Thread: \dt is listing tables from all databases
Greetings, I've got a postgresql server running 8.1.10 on Linux. I've noticed that when i'm logged into one of the databases, and I run '\dt' its listing every table from every database on the server (rather than just the tables in the current database instance). To make matters even more strange, if I log into any of the other databases and run '\dt' it only lists the tables in that database (as expected). I'm connecting as the same user every time. Any ideas why this is happening? thanks
Lonni J Friedman <netllama@gmail.com> writes: > I've got a postgresql server running 8.1.10 on Linux. I've noticed > that when i'm logged into one of the databases, and I run '\dt' its > listing every table from every database on the server (rather than > just the tables in the current database instance). To make matters > even more strange, if I log into any of the other databases and run > '\dt' it only lists the tables in that database (as expected). I'm > connecting as the same user every time. It's physically impossible for \dt to behave that way --- it simply doesn't have access to the catalogs for other databases. I'm going to read between the lines and guess about what really happened, though. By default, CREATE DATABASE clones what is in the "template1" database. If you thoughtlessly create some tables in that database, they'll get copied into other databases created subsequently. Lather, rinse, repeat enough times, and it might look like the later-created databases contain copies of everything else. Another possibility is you pointed a pg_restore operation at the wrong database and it loaded up copies of tables you meant to put somewhere else. regards, tom lane
On Thu, Jan 14, 2010 at 12:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Lonni J Friedman <netllama@gmail.com> writes: >> I've got a postgresql server running 8.1.10 on Linux. I've noticed >> that when i'm logged into one of the databases, and I run '\dt' its >> listing every table from every database on the server (rather than >> just the tables in the current database instance). To make matters >> even more strange, if I log into any of the other databases and run >> '\dt' it only lists the tables in that database (as expected). I'm >> connecting as the same user every time. > > It's physically impossible for \dt to behave that way --- it simply > doesn't have access to the catalogs for other databases. > > I'm going to read between the lines and guess about what really > happened, though. By default, CREATE DATABASE clones what is in the > "template1" database. If you thoughtlessly create some tables in that > database, they'll get copied into other databases created subsequently. > Lather, rinse, repeat enough times, and it might look like the > later-created databases contain copies of everything else. > > Another possibility is you pointed a pg_restore operation at the wrong > database and it loaded up copies of tables you meant to put somewhere > else. Thanks Tom. Actually, it turns out its the last one. After asking around, someone did erroneously try to restore the wrong database.