Re: psql: FATAL: role "postgres" does not exist

Hello,

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).

SELECT
relname IS NULL AS relame_is_null,
isdirty,
COUNT(*) AS count
FROM
pg_buffercache b
LEFT OUTER JOIN pg_class c ON b.relfilenode=c.relfilenode
GROUP BY
relame_is_null,
isdirty
;

relame_is_null isdirty count
false false 8914
true false 7347
true true 123

Any ideas of this behavior because inserts should have assigned relnames,
shouldn't they?

Thnx.

Ciao,
Gerhard

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

Gerhard Wiesinger <lists(at)wiesinger(dot)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(at)wiesinger(dot)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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sat, Sep 26, 2009 at 1:23 PM, Gerhard Wiesinger <lists(at)wiesinger(dot)com> wrote:
> On Sat, 26 Sep 2009, Tom Lane wrote:
>
>> Gerhard Wiesinger <lists(at)wiesinger(dot)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&publ=WLHMTAG&crea=TEXT_MFEHPG_Core_tagline_try bing_1x1

2009/9/26 Martin Gainty <mgainty(at)hotmail(dot)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(at)hotmail(dot)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(dot)marlowe(at)gmail(dot)com
>> To: mgainty(at)hotmail(dot)com
>> CC: pgsql-general(at)postgresql(dot)org
>>
>> 2009/9/26 Martin Gainty <mgainty(at)hotmail(dot)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(dot)marlowe(at)gmail(dot)com
> To: mgainty(at)hotmail(dot)com
> CC: pgsql-general(at)postgresql(dot)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(at)hotmail(dot)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(dot)marlowe(at)gmail(dot)com
> >> To: mgainty(at)hotmail(dot)com
> >> CC: pgsql-general(at)postgresql(dot)org
> >>
> >> 2009/9/26 Martin Gainty <mgainty(at)hotmail(dot)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&form=MLOGEN&publ=WLHMTAG&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(at)hotmail(dot)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(dot)marlowe(at)gmail(dot)com
>> To: mgainty(at)hotmail(dot)com
>> CC: pgsql-general(at)postgresql(dot)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(at)hotmail(dot)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(dot)marlowe(at)gmail(dot)com
>> >> To: mgainty(at)hotmail(dot)com
>> >> CC: pgsql-general(at)postgresql(dot)org
>> >>
>> >> 2009/9/26 Martin Gainty <mgainty(at)hotmail(dot)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(at)hotmail(dot)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(at)domain(dot)microsoft(dot)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(dot)marlowe(at)gmail(dot)com
> To: mgainty(at)hotmail(dot)com
> CC: pgsql-general(at)postgresql(dot)org
>
> On Sat, Sep 26, 2009 at 8:10 PM, Martin Gainty <mgainty(at)hotmail(dot)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(at)hotmail(dot)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?