[ADMIN] pg_attribute file in PostgreSQL 9.0
Hello, I have a question regarding pg_attribute. In which file it is stored because the relfilenode for it shows 0 and file 1249 isn't present in the folder. -- Łukasz Brodziak -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_attribute file in PostgreSQL 9.0
On 03/07/2012 05:14 AM, Lukasz Brodziak wrote:
Hello,
I have a question regarding pg_attribute. In which file it is stored
because the relfilenode for it shows 0 and file 1249 isn't present in
the folder.
select pg_relation_filepath('pg_attribute');
From the docs, relfilenode is:
Name of the on-disk file of this relation; zero means this is a
mapped relation whose disk file name is determined by low-level state
However the meaning mapped relation and low-level state are
difficult to divine from the docs. Go with the function.
Cheers,
Steve
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_attribute file in PostgreSQL 9.0
Steve Crawford scrawf...@pinpointresearch.com writes:
On 03/07/2012 05:14 AM, Lukasz Brodziak wrote:
I have a question regarding pg_attribute. In which file it is stored
because the relfilenode for it shows 0 and file 1249 isn't present in
the folder.
select pg_relation_filepath('pg_attribute');
From the docs, relfilenode is:
Name of the on-disk file of this relation; zero means this is a
mapped relation whose disk file name is determined by low-level state
However the meaning mapped relation and low-level state are
difficult to divine from the docs. Go with the function.
Or use pg_relation_filenode() if you want the result of the mapping.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] table names seem identical
how I can differentiate these: oamp=# \z public.c3* Access privileges Schema | Name | Type | Access privileges | Column access privileges +---+---+---+-- public | c3p0_connection_test | table | | public | c3p0_connection_test | table | | (2 rows) oamp=# select * from pg_tables where tablename = 'c3p0_connection_test'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +--++++--+- public | c3p0_connection_test | admin || f | f | f (1 row) oamp=# select version(); version PostgreSQL 9.0.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 32-bit (1 row) -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] speedup pg_dumpall
Am Donnerstag, 23. Februar 2012, 11:08:29 schrieb Kevin Grittner: What can I do to improve pg_dump performance? I can't help wondering why you're running it. For routine backups it is generally not your best option in PostgreSQL. PITR or warm standby are generally more useful for routine backups of any database big enough to worry about run time. We're a hoster, so usually each database belongs to a different customer. Each customer can have a get a dump of our database daily dump. Also, restoring a single db is simpler when you got a dump of it. For my purposes, pg_dump is mostly useful for dumping schema as SQL statements or dumping data from individual tables. Prior to the availability of pg_upgrade our main use was for upgrades from one major release to another, but I now see it as a pretty narrow niche utility -- at least for my shop. Agreed, for a classical db server this could be better. We need the per-db dumps, so pg_dump performance tuning would be nice. It's quite slow currently. Someone got hints what to do against that? -- mit freundlichen Grüssen, Michael Monnerie, Ing. BSc it-management Internet Services: Protéger http://proteger.at [gesprochen: Prot-e-schee] Tel: +43 660 / 415 6531 signature.asc Description: This is a digitally signed message part.
Re: [ADMIN] pg_attribute file in PostgreSQL 9.0
Thanks a lot I have found file I needed let's hope I will be able to
fix the problem now as I cannot connect to the database because I get
'catalog is missing 1 attribute for relation 2662' which I hope to be
able to repair by getting the pg_attribute file from another db.
2012/3/7 Tom Lane t...@sss.pgh.pa.us:
Steve Crawford scrawf...@pinpointresearch.com writes:
On 03/07/2012 05:14 AM, Lukasz Brodziak wrote:
I have a question regarding pg_attribute. In which file it is stored
because the relfilenode for it shows 0 and file 1249 isn't present in
the folder.
select pg_relation_filepath('pg_attribute');
From the docs, relfilenode is:
Name of the on-disk file of this relation; zero means this is a
mapped relation whose disk file name is determined by low-level state
However the meaning mapped relation and low-level state are
difficult to divine from the docs. Go with the function.
Or use pg_relation_filenode() if you want the result of the mapping.
regards, tom lane
--
Łukasz Brodziak
Do you bury me when I'm gone
Do you teach me while I'm here
Just as soon I belong
Then it's time I disappear
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_attribute file in PostgreSQL 9.0
On 03/07/2012 09:03 AM, Lukasz Brodziak wrote: Thanks a lot I have found file I needed let's hope I will be able to fix the problem now as I cannot connect to the database because I get 'catalog is missing 1 attribute for relation 2662' which I hope to be able to repair by getting the pg_attribute file from another db. STOP! Don't touch anything just yet. Now we know the real reason for the question. Do you have an idea what may have caused this? Are there any relevant errors in the log? What is the source of the file that you want to use as a replacement? What version of PG? OS? Is the database able to start? Are you attempting to connect as a superuser? With this as a starting point you may get some valuable advice (though from those people far more capable of answering this question than I). Cheers, Steve -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_attribute file in PostgreSQL 9.0
Steve Crawford scrawf...@pinpointresearch.com writes: On 03/07/2012 09:03 AM, Lukasz Brodziak wrote: Thanks a lot I have found file I needed let's hope I will be able to fix the problem now as I cannot connect to the database because I get 'catalog is missing 1 attribute for relation 2662' which I hope to be able to repair by getting the pg_attribute file from another db. STOP! Don't touch anything just yet. Now we know the real reason for the question. ... yeah, and this is the wrong answer. It's *exceedingly* unlikely that plopping in the pg_attribute file from a different database will accomplish anything except to make matters far worse. Even if the other DB has exactly the same tables and exactly the same columns in those tables, it likely doesn't have the same OID assignments. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] table names seem identical
On Wed, 07 Mar 2012 10:39:48 -0500, Ray Stell wrote: how I can differentiate these: oamp=# \z public.c3* Access privileges Schema | Name | Type | Access privileges | Column access privileges +---+---+--- +-- public | c3p0_connection_test | table | | public | c3p0_connection_test | table | | (2 rows) oamp=# select * from pg_tables where tablename = 'c3p0_connection_test'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +--++ ++--+- public | c3p0_connection_test | admin || f | f| f (1 row) misc=# create table test1(flag varchar(1)); CREATE TABLE misc=# create table test1 (flag varchar(1)); CREATE TABLE -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_attribute file in PostgreSQL 9.0
Ok, so the case is that I don't have the exact cause of the problem as it occured on one of our clients dbs. The PG version is 9.0.0 and the OS is windows XP. The DB is able to start (I have the data folder) but when I connect to the db from our application (or pgAdmin) I come across the above mentioned error. I have managed to get into the database after replacing the pg_attribute from another DB (clean db created with our installer) but then I read Your mail so I stopped at this point. Pg_log has only this one information on the error. 2012/3/7 Tom Lane t...@sss.pgh.pa.us: Steve Crawford scrawf...@pinpointresearch.com writes: On 03/07/2012 09:03 AM, Lukasz Brodziak wrote: Thanks a lot I have found file I needed let's hope I will be able to fix the problem now as I cannot connect to the database because I get 'catalog is missing 1 attribute for relation 2662' which I hope to be able to repair by getting the pg_attribute file from another db. STOP! Don't touch anything just yet. Now we know the real reason for the question. ... yeah, and this is the wrong answer. It's *exceedingly* unlikely that plopping in the pg_attribute file from a different database will accomplish anything except to make matters far worse. Even if the other DB has exactly the same tables and exactly the same columns in those tables, it likely doesn't have the same OID assignments. regards, tom lane -- Łukasz Brodziak Do you bury me when I'm gone Do you teach me while I'm here Just as soon I belong Then it's time I disappear -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_attribute file in PostgreSQL 9.0
Tom Lane t...@sss.pgh.pa.us wrote: Steve Crawford scrawf...@pinpointresearch.com writes: On 03/07/2012 09:03 AM, Lukasz Brodziak wrote: Thanks a lot I have found file I needed let's hope I will be able to fix the problem now as I cannot connect to the database because I get 'catalog is missing 1 attribute for relation 2662' which I hope to be able to repair by getting the pg_attribute file from another db. STOP! Don't touch anything just yet. Now we know the real reason for the question. ... yeah, and this is the wrong answer. It's *exceedingly* unlikely that plopping in the pg_attribute file from a different database will accomplish anything except to make matters far worse. Even if the other DB has exactly the same tables and exactly the same columns in those tables, it likely doesn't have the same OID assignments. http://wiki.postgresql.org/wiki/Corruption -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] table names seem identical
On Wed, Mar 07, 2012 at 05:41:41PM +, Walter Hurry wrote: On Wed, 07 Mar 2012 10:39:48 -0500, Ray Stell wrote: misc=# create table test1(flag varchar(1)); CREATE TABLE misc=# create table test1 (flag varchar(1)); CREATE TABLE yeah, that is the case, the \z output is truncated it seems: Schema | Name | Type | Access privileges | Column access privileges +---+---+---+-- public | c3p0_connection_test | table | | public | c3p0_connection_test | table | | (2 rows) oamp=# select * from pg_tables where tablename = 'c3p0_connection_test '; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +---++++--+- public | c3p0_connection_test | admin || f | f | f (1 row) oamp=# select * from pg_tables where tablename = 'c3p0_connection_test'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +--++++--+- public | c3p0_connection_test | admin || f | f | f (1 row) -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] logging issue
Thanks so much for the response. Logging works great. Many thanks to the PostgreSQL development team for building such a wonderful database. 9.1 is working great. Thanks, Lance Campbell Software Architect Web Services at Public Affairs 217-333-0382 -Original Message- From: Jerry Sievers [mailto:gsiever...@comcast.net] Sent: Tuesday, March 06, 2012 3:43 PM To: Campbell, Lance Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] logging issue Campbell, Lance la...@illinois.edu writes: PostgreSQL 9.1.3 I just moved our test database from PostgreSQL 9.0 to 9.1. I also changed my log settings. I am seeing every single SQL statement that PostgreSQL performs by my Java applications. Is there some way to only display the details when there is an error? I would assume yes. But for some reason I am getting all SQL statements. I just want the errors. I have included below my settings for the logging section. Any help would be greatly appreciated. Thanks! Change log_statement to 'none' and pg_ctl reload the server. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 305.321.1144 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Postgres server crashing unexpectedly.
Version : 8.3 Below is the log from the server. ---
Re: [ADMIN] table names seem identical
On 03/07/2012 07:39 AM, Ray Stell wrote:
how I can differentiate these:
oamp=# \z public.c3*
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
+---+---+---+--
public | c3p0_connection_test | table | |
public | c3p0_connection_test | table | |
(2 rows)
oamp=# select * from pg_tables where tablename = 'c3p0_connection_test';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
+--++++--+-
public | c3p0_connection_test | admin || f | f
| f
(1 row)
oamp=# select version();
version
PostgreSQL 9.0.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-51), 32-bit
(1 row)
do you really want to differentiate or are you just pointing out that
it's difficult to tell via \z?
because if you really need to know then you can do
psql -E temp
temp=# \z test*
* QUERY **
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S'
THEN 'sequence' END as Type,
pg_catalog.array_to_string(c.relacl, E'\n') AS Access privileges,
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl,
E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS Column access privileges
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S')
AND c.relname ~ '^(test.*)$'
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
**
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
+---+---+---+--
public | test | table | |
public | test | table | |
(2 rows)
Now you have the query, so alter it to:
SELECT n.nspname as Schema,
* 'x'||c.relname||'x' as Name,
* CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S'
THEN 'sequence' END as Type,
pg_catalog.array_to_string(c.relacl, E'\n') AS Access privileges,
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl,
E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS Column access privileges
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'S')
AND c.relname ~ '^(test.*)$'
AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
and you'll get:
Schema | Name | Type | Access privileges | Column access privileges
+-+---+---+--
public | xtestx | table | |
public | xtest x | table | |
(2 rows)
