pg_buffercache - A lot of "unnamed" relfilenodes?

Gerhard Wiesinger <lists@wiesinger.com> writes:

Research on the pg_bufferache showed that a lot of pages don't have any
assigned relfilenodesin pg_class, even when they are dirty (in this case
inserts are done).

There should never be any buffered pages for nonexistent tables. The
method you are using will only show relnames for pages belonging to
tables of the current database ... maybe there are a lot of dirty pages
for other databases in the cluster? Or maybe the tables are newly
created (not committed) and so you can't see their pg_class rows yet?

regards, tom lane

On Sat, 26 Sep 2009, Tom Lane wrote:

Gerhard Wiesinger <lists@wiesinger.com> writes:

Research on the pg_bufferache showed that a lot of pages don't have any
assigned relfilenodesin pg_class, even when they are dirty (in this case
inserts are done).

There should never be any buffered pages for nonexistent tables. The
method you are using will only show relnames for pages belonging to
tables of the current database ... maybe there are a lot of dirty pages
for other databases in the cluster? Or maybe the tables are newly
created (not committed) and so you can't see their pg_class rows yet?

OK, this is a visibility problem of the databases. Is it possible to GRANT
visibility for e.g. one user to all databases for pg_class?

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/

What I ended up doing to work around not seeing anything besides your
active database for my own tests was write a simple shell script that
connected to all of the database in succession, saving everything in the
catalog that pg_buffercache needs to join against to a table. Then I
joined against that table rather than the catalog entries. This is
potentially a security issue on a real system, but if you're the admin and
want to analyze the whole database, it's easy enough to make that table
invisible to everyone but you.

Don't have that code available to give out, but it wasn't hard to write;
something like this did the main work:

#!/bin/bash
dbs=`psql -At -c "select datname from pg_database"`
for d in $dbs
do
psql -d $d -c "INSERT INTO full_catalog SELECT relname, relfilenode, (SELECT oid FROM pg_database WHERE datname = current_database()) from
pg_class"
done

Add a simple schema that follows the structure of the fields in pg_class
and pg_database needed and you're off. You may still get unnamed stuff
that's just not visible yet, the cross-database stuff was the source for
most of the missing bits I ran into.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Sat, Sep 26, 2009 at 1:23 PM, Gerhard Wiesinger <lists@wiesinger.com> wrote:

On Sat, 26 Sep 2009, Tom Lane wrote:

Gerhard Wiesinger <lists@wiesinger.com> writes:

Research on the pg_bufferache showed that a lot of pages don't have any
assigned relfilenodesin pg_class, even when they are dirty (in this case
inserts are done).

There should never be any buffered pages for nonexistent tables.  The
method you are using will only show relnames for pages belonging to
tables of the current database ... maybe there are a lot of dirty pages
for other databases in the cluster?  Or maybe the tables are newly
created (not committed) and so you can't see their pg_class rows yet?

OK, this is a visibility problem of the databases. Is it possible to GRANT
visibility for e.g. one user to all databases for pg_class?

It's not a question of granting it, it's just not there in the other
databases. you could join on a dblink'd result set I guess.

All-

when starting postgres what does this error mean exactly?
psql: FATAL: role "postgres" does not exist

what is the exact solution for psql: FATAL: role "postgres" does not exist error?

thank you
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

_________________________________________________________________
Microsoft brings you a new way to search the web. Try Bing™ now
http://www.bing.com?form=MFEHPG&amp;publ=WLHMTAG&amp;crea=TEXT_MFEHPG_Core_tagline_try bing_1x1

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

All-

when starting postgres what does this error mean exactly?
psql: FATAL:  role "postgres" does not exist

what is the exact solution for psql: FATAL:  role "postgres" does not exist
error?

That's not an error starting postgresql, it's an error starting psql.
Postgresql, by default, sets ownership of the cluster files and
creates a login role for the username used to run initdb. So, what
username was used to run initdb?

So, did you delete the postgres user? What happens if you bring up a
backend in single user mode? Can you see the users then with \du?

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

postgres
?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.

Date: Sat, 26 Sep 2009 19:29:30 -0600
Subject: Re: [GENERAL] psql: FATAL: role "postgres" does not exist
From: scott.marlowe@gmail.com
To: mgainty@hotmail.com
CC: pgsql-general@postgresql.org

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

All-

when starting postgres what does this error mean exactly?
psql: FATAL:  role "postgres" does not exist

what is the exact solution for psql: FATAL:  role "postgres" does not
exist
error?

That's not an error starting postgresql, it's an error starting psql.
Postgresql, by default, sets ownership of the cluster files and
creates a login role for the username used to run initdb. So, what
username was used to run initdb?

________________________________
Bing™ brings you maps, menus, and reviews organized in one place. Try it
now.

--
When fascism comes to America, it will be intolerance sold as diversity.

cant get in at all with psql
netstat shows the listener is up on 5432

trying with pgAdmin3
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Sat, 26 Sep 2009 19:44:09 -0600
Subject: Re: [GENERAL] psql: FATAL: role "postgres" does not exist
From: scott.marlowe@gmail.com
To: mgainty@hotmail.com
CC: pgsql-general@postgresql.org

So, did you delete the postgres user? What happens if you bring up a
backend in single user mode? Can you see the users then with \du?

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

postgres
?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.

Date: Sat, 26 Sep 2009 19:29:30 -0600
Subject: Re: [GENERAL] psql: FATAL: role "postgres" does not exist
From: scott.marlowe@gmail.com
To: mgainty@hotmail.com
CC: pgsql-general@postgresql.org

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

All-

when starting postgres what does this error mean exactly?
psql: FATAL: role "postgres" does not exist

what is the exact solution for psql: FATAL: role "postgres" does not
exist
error?

That's not an error starting postgresql, it's an error starting psql.
Postgresql, by default, sets ownership of the cluster files and
creates a login role for the username used to run initdb. So, what
username was used to run initdb?

________________________________
Bing™ brings you maps, menus, and reviews organized in one place. Try it
now.

--
When fascism comes to America, it will be intolerance sold as diversity.

_________________________________________________________________
Bing™ brings you maps, menus, and reviews organized in one place. Try it now.
http://www.bing.com/search?q=restaurants&amp;form=MLOGEN&amp;publ=WLHMTAG&amp;crea=TEXT_MLOGEN_Core_tagline_local_1x1

So, did you delete the postgres user?
Go here:
http://www.postgresql.org/docs/8.4/static/app-postgres.html
and look for single user mode. It's likely what you need.

Not surprised you can't get in with psql, you seem to not have a super
user account (or possibly any account)

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

cant get in at all with psql
netstat shows the listener is up on 5432

trying with pgAdmin3
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.

Date: Sat, 26 Sep 2009 19:44:09 -0600
Subject: Re: [GENERAL] psql: FATAL: role "postgres" does not exist
From: scott.marlowe@gmail.com
To: mgainty@hotmail.com
CC: pgsql-general@postgresql.org

So, did you delete the postgres user? What happens if you bring up a
backend in single user mode? Can you see the users then with \du?

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

postgres
?
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede
unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese
Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas
le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la
copie
de ceci est interdite. Ce message sert à l'information seulement et
n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les
email
peuvent facilement être sujets à la manipulation, nous ne pouvons
accepter
aucune responsabilité pour le contenu fourni.

Date: Sat, 26 Sep 2009 19:29:30 -0600
Subject: Re: [GENERAL] psql: FATAL: role "postgres" does not exist
From: scott.marlowe@gmail.com
To: mgainty@hotmail.com
CC: pgsql-general@postgresql.org

2009/9/26 Martin Gainty <mgainty@hotmail.com>:

All-

when starting postgres what does this error mean exactly?
psql: FATAL:  role "postgres" does not exist

what is the exact solution for psql: FATAL:  role "postgres" does not
exist
error?

That's not an error starting postgresql, it's an error starting psql.
Postgresql, by default, sets ownership of the cluster files and
creates a login role for the username used to run initdb. So, what
username was used to run initdb?

________________________________
Bing™ brings you maps, menus, and reviews organized in one place. Try it
now.

--
When fascism comes to America, it will be intolerance sold as diversity.

________________________________
Bing™ brings you maps, menus, and reviews organized in one place. Try it
now.

--
When fascism comes to America, it will be intolerance sold as diversity.

On Sat, Sep 26, 2009 at 8:10 PM, Martin Gainty <mgainty@hotmail.com> wrote:

partial victory but
im baffled i started all commands with RUNAS /user:postgres
cd /postgres/pgctl/bin
RUNAS /user:postgres "initdb.exe -D /data"
RUNAS /user:postgres ""pg_ctl.exe" -D "/data" -l logfile start"
RUNAS /user:postgres "postgres.exe -D /data"

but to get into postgres i have to
psql -u Admin

now when i do
postgres-# \du
              List of roles
   Role name   | Attributes  | Member of
---------------+-------------+-----------
 Admin | Superuser   | {}
          : Create role
          : Create DB

yes i initially logged in as Admin
but I ran all the commands thru RUNAS /user:postgres

currently unable to login as postgres as I have webservers,appservers
running under 'admin' space

I'm confused as to the install under admin when i did the runas?
recommendations?

You've exhausted my knowledge of running pg under windows I'm afraid.
Must be how the windows installer works or something?

i prefer cygwin as all the unix commands are available but mkuser and groupadd arent up yet..ugh
i have a bunch of apps already running..webapp servers..tomcat..glassfish..weblogic
and 2 other databases running and because I need god access (I cant run any of these as postgres user)

here is the rundown on RUNAS

runas /noprofile /user:mymachine\administrator cmd
runas /profile /env /user:mydomain\admin "mmc %windir%\system32\dsa.msc"
runas /env /user:user@domain.microsoft.com "notepad \"my file.txt\""

whats *supposed to* happen is

RUNAS will run any command (the question is are we working with Windows Native Binaries or cygwin?)
with regards to cygwin
i can build postmaster.exe postgres.exe initdb.exe, libpgport_srv.dll and all the binaries using gcc and ld

not a big blocker.. I have a psql command prompt ..i can create roles users databases and tables now

btw:those ipc libraries seemed to be wrapped around the axle of elog statements but thats another topic
i commented out elog and replaced with printf..and all the ipc errors went away

thanks scott!
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

Date: Sat, 26 Sep 2009 20:12:15 -0600
Subject: Re: [GENERAL] psql: FATAL: role "postgres" does not exist
From: scott.marlowe@gmail.com
To: mgainty@hotmail.com
CC: pgsql-general@postgresql.org

On Sat, Sep 26, 2009 at 8:10 PM, Martin Gainty <mgainty@hotmail.com> wrote:

partial victory but
im baffled i started all commands with RUNAS /user:postgres
cd /postgres/pgctl/bin
RUNAS /user:postgres "initdb.exe -D /data"
RUNAS /user:postgres ""pg_ctl.exe" -D "/data" -l logfile start"
RUNAS /user:postgres "postgres.exe -D /data"

but to get into postgres i have to
psql -u Admin

now when i do
postgres-# \du
List of roles
Role name | Attributes | Member of
---------------+-------------+-----------
Admin | Superuser | {}
: Create role
: Create DB

yes i initially logged in as Admin
but I ran all the commands thru RUNAS /user:postgres

currently unable to login as postgres as I have webservers,appservers
running under 'admin' space

I'm confused as to the install under admin when i did the runas?
recommendations?

You've exhausted my knowledge of running pg under windows I'm afraid.
Must be how the windows installer works or something?

_________________________________________________________________
Hotmail® has ever-growing storage! Don’t worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009

Martin Gainty wrote:

whats *supposed to* happen is
RUNAS will run any command (the question is are we working with
Windows Native Binaries or cygwin?)
with regards to cygwin
i can build postmaster.exe postgres.exe initdb.exe, libpgport_srv.dll
and all the binaries using gcc and ld

cygwin compiled apps are still native win32 binaries, they are just are
using the services of the cygwin dll rather than regular win32 api calls.

btw, mingW has mSys, which also provides a set of unix style Gnu
commands, but these execute more natively, without a DLL like the cygwin
providinig faked unix API calls.

yes, mSys isn't as comprehensive as cygwin, however, its all the basic
stuff most folks need.

me, I'd rather go ahead and run my unix-ish stuff on a real unix server,
either a remote server, or something running in a vm (using virtualbox,
vmware server, etc)

On Sat, Sep 26, 2009 at 8:26 PM, Martin Gainty <mgainty@hotmail.com> wrote:

i prefer cygwin as all the unix commands are available but mkuser and
groupadd arent up yet..ugh

You might be happier with VMs running BSD or some linux flavor. If
you have enough memory and 64 bit windows it should run well enough.
Being able to toss another server up for testing is useful.

whats *supposed to* happen is
RUNAS will run any command (the question is are we working with Windows
Native Binaries or cygwin?)

yeah. I think that within cygwin you might be running as a windows
user not unix user. Can you sudo su - within the cygwin environment?
Or some kind of sudo like unixy command?

not a big blocker.. I have a psql command prompt ..i can create roles users
databases and tables now

I fell in love with psql when I first ran psql. I knew the db
couldn't suck because it had the best text interface I'd ever seen for
a db. :) I'll \ commands and \h syntax diagrams for $2000 alex...

btw:those ipc libraries seemed to be wrapped around the axle of elog
statements but thats another topic
i commented out elog and replaced with printf..and all the ipc errors went

Ya lost me there. :) Is this a windows exclusive kinda thing?