2

I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine: psql -qAtX -h *** -p *** -U postgres -c "select count(*) from pg_buffercache where isdirty", so I am expecting to get a pure decimal value for parsing. However, I get relation "pg_buffercache" does not exist error. I have already added pg_buffercache extension (CREATE EXTENSION pg_buffercache;), restarted server afterwards and checked if it is present:

postgres=# \dx
pg_buffercache | 1.0    | public     | examine the shared buffer cache

The strangest thing is, if i execute this query directly in postgresql shell, it works just fine:

postgres=# select count(*) from pg_buffercache where isdirty;
count
-------
 113

What can cause this? Other queries not using pg_buffercache work perfectly.

2
  • 1
    Perhaps your psql -qAtX command is not actually connecting to the "postgres" database (or other database where you have pg_buffercache insatlled). One way this might happen is if you had env. variable PGDATABASE defined. Can you try adding a database (e.g. -d postgres) to your psql command? Commented Jun 3, 2016 at 15:41
  • You are right. I was obviously missing the database name. Should put more research into this. Thanks! Commented Jun 3, 2016 at 16:10

1 Answer 1

1

You need to first connect to the database that you need to check the stats from, and in that do the create extension, after which the query should work ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.