[HACKERS] $PGDATA/base/???
On older versions of PG, 7.0 included, in the $PDGATA/base folder you could see the names of the databases for that $PGDATA. Now all I see is: $ ls -l total 16 drwx-- 2 postgres wheel 1536 Jan 12 15:42 1 drwx-- 2 postgres wheel 1536 Jan 12 15:41 18719 drwx-- 2 postgres wheel 1536 Jan 12 15:42 18720 drwx-- 2 postgres wheel 1536 Jan 15 15:59 18721 Is there a way to relate this to the names of the databases? Why the change? Or am I missing something key here.. - Brandon b. palmer, [EMAIL PROTECTED] pgp: www.crimelabs.net/bpalmer.pgp5
Re: [HACKERS] $PGDATA/base/???
bpalmer wrote: > > On older versions of PG, 7.0 included, in the $PDGATA/base folder you > could see the names of the databases for that $PGDATA. Now all I see is: No longer. > Is there a way to relate this to the names of the databases? Why the > change? Or am I missing something key here.. See the thread on the renaming in the archives. In short, this is part of Vadim's work on WAL -- the new naming makes certain things easier for WAL. Utilities to relate the new names to the actual database/table names _do_ need to be written, however. The information exists in one of the system catalogs now -- it just has to be made accessible. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [HACKERS] $PGDATA/base/???
> > Is there a way to relate this to the names of the databases? Why the > > change? Or am I missing something key here.. > > See the thread on the renaming in the archives. In short, this is part > of Vadim's work on WAL -- the new naming makes certain things easier for > WAL. > > Utilities to relate the new names to the actual database/table names > _do_ need to be written, however. The information exists in one of the > system catalogs now -- it just has to be made accessible. Yes, I am hoping to write this utility before 7.1 final. Maybe it will have to be in /contrib. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] $PGDATA/base/???
Wow, this looks great, and it worked the first time too. I will commit if no one makes objects. > > > > Is there a way to relate this to the names of the databases? Why the > > > > change? Or am I missing something key here.. > > > > > > See the thread on the renaming in the archives. In short, this is part > > > of Vadim's work on WAL -- the new naming makes certain things easier for > > > WAL. > > > > > > Utilities to relate the new names to the actual database/table names > > > _do_ need to be written, however. The information exists in one of the > > > system catalogs now -- it just has to be made accessible. > > > > Yes, I am hoping to write this utility before 7.1 final. Maybe it will > > have to be in /contrib. > > I just finished writing such an app. Take a look. It's in a format > that can be put in /contrib. Let me know if you want any changes made, > etc. Feel free to use any of the code you wish. > > http://www.crimelabs.net/postgresql.shtml > > - Brandon > > b. palmer, [EMAIL PROTECTED] > pgp: www.crimelabs.net/bpalmer.pgp5 > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] $PGDATA/base/???
On Wed, Jan 17, 2001 at 05:49:36PM -0500, Bruce Momjian wrote: > Wow, this looks great, and it worked the first time too. I will commit > if no one makes objects. > I object. The code displays oids and tablenames or relnames. Oid is just the initial, default filename for tables, and may change to something other than the oid. Currently, the reindex code is the only place that could change the relfilenode without changing the oid, but I think there may be more in the future. Here's a patch to Brandon's code (completely untested, BTW): Ross *** oid2name.c.orig Wed Jan 17 17:12:05 2001 --- oid2name.c Wed Jan 17 17:27:11 2001 *** *** 331,339 /* don't exclude the systables if this is set */ if(systables == 1) ! sprintf(todo, "select oid,relname from pg_class order by relname"); else ! sprintf(todo, "select oid,relname from pg_class where relname not like 'pg_%%' order by relname"); sql_exec(conn, todo, NULL); } --- 331,339 /* don't exclude the systables if this is set */ if(systables == 1) ! sprintf(todo, "select relfilenode,relname from pg_class order by relname"); else ! sprintf(todo, "select relfilenode,relname from pg_class where relname not like 'pg_%%' order by relname"); sql_exec(conn, todo, NULL); } *** *** 348,354 todo = (char *) malloc (1024); /* get the oid and tablename where the name matches tablename */ ! sprintf(todo, "select oid,relname from pg_class where relname = '%s'", tablename); returnvalue = sql_exec(conn, todo, 1); --- 348,354 todo = (char *) malloc (1024); /* get the oid and tablename where the name matches tablename */ ! sprintf(todo, "select relfilenode,relname from pg_class where relname = '%s'", tablename); returnvalue = sql_exec(conn, todo, 1); *** *** 372,378 todo = (char *) malloc (1024); ! sprintf(todo, "select oid,relname from pg_class where oid = %i", oid); returnvalue = sql_exec(conn, todo, 1); --- 372,378 todo = (char *) malloc (1024); ! sprintf(todo, "select relfilenode,relname from pg_class where relfilenode = %i", oid); returnvalue = sql_exec(conn, todo, 1);
Re: [HACKERS] $PGDATA/base/???
> I object. The code displays oids and tablenames or relnames. Oid is just > the initial, default filename for tables, and may change to something other > than the oid. Currently, the reindex code is the only place that could change > the relfilenode without changing the oid, but I think there may be more > in the future. Looks great, and I agree. Did not know that little piece of information. I have made the changed to my code, here's the new version. I have tested this one and updated the web page. - brandon b. palmer, [EMAIL PROTECTED] pgp: www.crimelabs.net/bpalmer.pgp5 oid2name-0.1.1.tar.gz
Re: [HACKERS] $PGDATA/base/???
"Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: > I object. The code displays oids and tablenames or relnames. Oid is just > the initial, default filename for tables, and may change to something other > than the oid. Currently, the reindex code is the only place that could change > the relfilenode without changing the oid, but I think there may be more > in the future. Right, relfilenode is the thing to look at, not OID. I believe we are thinking of using relfilenode updates for a number of things in the future --- CLUSTER and faster index rebuilds in VACUUM are two thoughts that come to mind ... regards, tom lane
Re: [HACKERS] $PGDATA/base/???
I have added this to /contrib for 7.1. > > I object. The code displays oids and tablenames or relnames. Oid is just > > the initial, default filename for tables, and may change to something other > > than the oid. Currently, the reindex code is the only place that could change > > the relfilenode without changing the oid, but I think there may be more > > in the future. > > Looks great, and I agree. Did not know that little piece of information. > I have made the changed to my code, here's the new version. I have > tested this one and updated the web page. > > - brandon > > > b. palmer, [EMAIL PROTECTED] > pgp: www.crimelabs.net/bpalmer.pgp5 > Content-Description: [ Attachment, skipping... ] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] $PGDATA/base/???
On Tue, 23 Jan 2001, Bruce Momjian wrote: > I have added this to /contrib for 7.1. > Not sure if you know this, but you checked in the code compiled and w/ the .o file... FYI. b. palmer, [EMAIL PROTECTED] pgp: www.crimelabs.net/bpalmer.pgp5
Re: [HACKERS] $PGDATA/base/???
> > What I will probably do is make a wrapper around it so it I can do: > > > > ls | oidmapper > > > > and see the files as table names. > > Hmmm I think I can add that to the code.. > > will try.. > It has to be pretty smart. Consider this: $ pwd /u/pg/data/base/18720 $ ls -l It has to read the directories above, looking for a directory name that is all numbers. It needs to then use that to find the database name. Of course, if you are not in the directory, you may have a problem with the database and require them to specify it on the command line. It then has to process the the contents of ls -l and find the oids in there and map them: total 2083 -rw--- 1 postgres postgres8192 Jan 15 23:43 1215 -rw--- 1 postgres postgres8192 Jan 15 23:43 1216 -rw--- 1 postgres postgres8192 Jan 15 23:43 1219 -rw--- 1 postgres postgres 24576 Jan 15 23:43 1247 -rw--- 1 postgres postgres 114688 Jan 19 21:43 1249 -rw--- 1 postgres postgres 229376 Jan 15 23:43 1255 -rw--- 1 postgres postgres 24576 Jan 15 23:59 1259 -rw--- 1 postgres postgres8192 Jan 15 23:43 16567 -rw--- 1 postgres postgres 16384 Jan 16 00:04 16579 The numbers <16k are system tables so you probably need code to lookup stuff <16k, and if it doesn't begin with pg_, it is not an oid. It also should handle 'du': $ du 1517./1 1517./18719 2085./18720 1517./27592 20561 ./27593 27198 . As you can see, this could be tricky. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] $PGDATA/base/???
Thanks. Removed. > On Tue, 23 Jan 2001, Bruce Momjian wrote: > > > I have added this to /contrib for 7.1. > > > Not sure if you know this, but you checked in the code compiled and w/ > the .o file... > > FYI. > > b. palmer, [EMAIL PROTECTED] > pgp: www.crimelabs.net/bpalmer.pgp5 > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
