Schema with OID 0 does not exist in pg_dump

Re: Segmentation fault

Kevin,
That did it.  Doing the command ulimit -c unlimited worked!

Thanks,

Lance
________________________________________
From: Kevin Grittner [Kevin.Grittner <at> wicourts.gov]
Sent: Wednesday, August 31, 2011 4:42 PM
To: Campbell, Lance; pgsql-admin <at> postgresql.org
Subject: RE: [ADMIN] Segmentation fault

"Campbell, Lance" <lance <at> illinois.edu> wrote:
> From: Kevin Grittner [Kevin.Grittner <at> wicourts.gov]

>> Can you get a core file from psql?

> I don't know how to get a core dump.  What do I do?

If running this shows a limit (especially zero):

ulimit -c

then run this in your shell before running psql:

ulimit -c unlimited

Look for a core file in your current directory after the failure.
If you get one, try running:

gdb --core=YOURCOREFILENAME

Once gdb is running, type:

bt

and post the results.

-Kevin

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Password when logging into postgres

Re: Schema with OID 0 does not exist in pg_dump

Lukasz Brodziak <lukasz.brodziak <at> gmail.com> writes:
> When I try to make a dump of a database I get the error pg_dump: schema with
> OID 0 does not exist. How can I find the schema with this OID?

Well, you can't, because it doesn't exist .  What you need to find
out is what is trying to reference it.  I'd start by looking for a zero
in one of the catalog columns that reference pg_namespace.oid, such as
pg_class.relnamespace.

			regards, tom lane

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Password when logging into postgres

On Thu, Sep 1, 2011 at 7:48 AM, Campbell, Lance <lance <at> illinois.edu> wrote:
> Postgres 9.0.4
> I installed from the visual linux installer
> Redhat 6 newest updates
>
> I reinstalled postgres from scratch receintly.  I recreated a role with
> createrole called XYZ.  I do not want XYZ to use a password on my local
> box.  How do I set this up so that it will not ask for a password?

two ways.

1: Set the pg server to just trust this user, connecting to x database
or all databases using the pg_hba.conf file
2: Set the normal md5 method in pg_hba.conf and use .pgpass file
(http://wiki.postgresql.org/wiki/Pgpass) to set a password for libpq
to use when that user logs into the db.

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Password when logging into postgres

 Hi Lance,

>  I reinstalled postgres from scratch receintly. I recreated a role
> with createrole called XYZ. I do not want XYZ to use a password on my
> local box. How do I set this up so that it will not ask for a
> password?

 You have two options:

 1) insert a line in ~/.pgpass file in your home directory for that 
 database user 
 (http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html)
 2) insert a 'trust' rule for that user in the pg_hba.conf for local 
 connections 
 (http://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html)

 I would personally go with #1 in production environment.

 Cheers,
 Gabriele

-- 
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini <at> 2ndQuadrant.it - www.2ndQuadrant.it

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

pg_restore -j mixed and missing lines of output

Hi,

I don't know if this is a known problem, or something I'm doing wrong,
but pg_restore -j tends to sometimes mix lines, or even completely
drop some. Here is an example (it's a -j12 run):

pg_restore: pg_restore: restoring data for table "edw_iu_paglac_axe1_kpi_lac_month_bh" 
restoring data for table "edw_iu_paglac_axe1_kpi_lac_month"

This is one case when nothing is lost, messages only get mixed. Sometimes, 
entire lines disappear (a table's data is restored, but I don't get the
'restoring' or 'finished' message lines).

This is on Linux, so looking at pg_restore's code, I read that workers are forked
and printing all on the same file descriptor without locking, so I understand
that messages are mixed like this example. I couldn't find any explanation to
the dropped messages though.

Anyway, I don't know if this is a known problem. I searched the mailing list
with no success. I can provide a full log of a complete restore, without and
with -j.

Thanks,

Marc

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Alter column varchar(n) via updating pg_attribute

The last time this was asked about 
(http://archives.postgresql.org/pgsql-admin/2010-02/msg00189.php), 9.0 
was still alpha.

Is there any reason to suspect that direct pg_attribute manipulation to 
expand a varchar(n) column might not play nice with streaming 
replication/hot standby?  (Naturally I'm also in the process of testing).

Thanks,
Geoff

---------------

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Alter column varchar(n) via updating pg_attribute

Geoff Tolley <geoff.tolley <at> yougov.com> writes:
> The last time this was asked about 
> (http://archives.postgresql.org/pgsql-admin/2010-02/msg00189.php), 9.0 
> was still alpha.

> Is there any reason to suspect that direct pg_attribute manipulation to 
> expand a varchar(n) column might not play nice with streaming 
> replication/hot standby?  (Naturally I'm also in the process of testing).

I can't see why it wouldn't.  The WAL/replication mechanism works at
such a low level that it has no idea whether an update to pg_attribute
originated in an official DDL command or a manual UPDATE of the catalog.
I think you should be more worried about fat-fingering the update than
whether it will be replicated correctly.

But, of course, testing is always a good idea 

			regards, tom lane

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: plsql gets "out of memory"

Hi Kevin,

I did another try with following additional changes based on our discussion:
1. use the tcp connection
2. turn off autovacuum
3. turn off full_page_writes

I could import more than 30G data in about 2 hours. That's totally 
acceptable performance to me with the current server capability.  There 
is a minor issue though. I saw a few errors during the import:
ERROR:  invalid byte sequence for encoding "UTF8": 0xe6272c
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5272c
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5272c
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5272c
ERROR:  invalid byte sequence for encoding "UTF8": 0xe68e27
ERROR:  invalid byte sequence for encoding "UTF8": 0xe7272c
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5272c
ERROR:  invalid byte sequence for encoding "UTF8": 0xe5a427

My data was exported from an UTF8 MySQL database and my pgsql db is also 
UTF8. I got 8 errors above only with about 3 million records imported. 
The strange thing is, I usually see the problematic SQL output in the 
log if there is any error for that SQL so I have a chance to fix the 
data manually. But for the errors above, I don't see any SQL logged. The 
pgsql log just output error log same as above with no additional info:
2011-09-01 11:26:32 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe6272c
2011-09-01 11:26:47 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe5272c
2011-09-01 11:26:53 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe5272c
2011-09-01 11:26:58 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe5272c
2011-09-01 11:26:58 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe68e27
2011-09-01 11:27:01 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe7272c
2011-09-01 11:27:06 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe5272c
2011-09-01 11:27:15 CST ERROR:  invalid byte sequence for encoding 
"UTF8": 0xe5a427

What could be the cause of that?

于 2011/8/30 9:29, Rural Hunter 写道:
> Thank you. I didn't understand what 'vacuum freeze' actually does. I 
> will check the detail to see if it's good for my situation. and I will 
> also test the load by tcp connection. Thanks again for all your 
> advices and they are really very helpful to me!
>
> 于 2011/8/30 0:06, Kevin Grittner 写道:
>> Rural Hunter<ruralhunter <at> gmail.com>  wrote:
>>> 2011/8/29 23:18, Kevin Grittner:
>>
>>>> I also recommend a VACUUM FREEZE ANALYZE on the database unless
>>>> most of these rows will be deleted or updated before you run a
>>>> billion database transactions. Otherwise you will get a painful
>>>> "anti-wraparound" autovacuum on everything, probably at a time
>>>> of heavy usage.
>>> hmm....I will try to turn autovacuum off though I didn't see any
>>> resource intension caused by it.
>>
>> Well, turning off autovacuum during  a bulk load is probably a net
>> gain if it's insert-only (i.e., no need to query just-loaded data to
>> decide what to do with new rows); but that's not what I was getting
>> at.  Bulk loading 200 GB of data which is not going to be deleted or
>> updated heavily is setting a performance time bomb without a VACUUM
>> FREEZE.  At some point, perhaps months later, it will be necessary
>> to freeze the tuples to prevent data loss, and since this occurs
>> based on a threshold of how many transaction IDs have been consumed,
>> it is most likely to happen at peak OLTP loads, when it will be the
>> biggest problem.  A VACUUM FREEZE (and you might as well throw in
>> ANALYZE while you're at it) will take care of that up front.  As a
>> side benefit it will keep SELECT statements from generating heavy
>> *write* loads on the first access to tuples, and will perform other
>> maintenance which will improve database performance.
>>
>> I just count the time for VACUUM FREEZE ANALYZE as part of the bulk
>> load time required before letting in users.
>>
>>>> Network latency?
>>
>>> No, I do the import locally on the db server so the network
>>> letency can be excluded.
>>
>> Hmm...  I don't remember the details, but there was a problem at
>> some point where Linux pipe connections could introduce significant
>> latency, and you could get much better performance on a TCP
>> connection through localhost.  It might be worth a try.  (Maybe
>> someone else will remember the details.)
>>
>> -Kevin
>>
>

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin