General discussion

YuChi | 24 May 2013 09:35

Help, How to start the server??

i use postgresql-9.2.4 install on ubuntu11.04(natty) 
and i use the command: 

*postgres <at> ubuntu:/$ postgres -D /usr/local/pgsql/data*

or use

*postgres <at> ubuntu:/$ /usr/local/pgsql/bin/postmaster -D
/usr/local/pgsql/data*

to start the server
but it's stopping at the message:

*LOG:  database system was shut down at 2013-05-24 15:07:34 CST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started*

for a long time
it seems never continue again
and i don't know how to do next...
can anyone tell me is there anything wrong? or how to do next?

thank you

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Help-How-to-start-the-server-tp5756725.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)

(Continue reading)

headers

JotaComm | 24 May 2013 14:46

Re: WAL contains references to invalid pages

Hello,

2013/5/21 Adarsh Sharma <eddy.adarsh <at> gmail.com>
Try to take backups of that table & index only. If succeeded drop and recreate them. May be it fix your issue.

On Monday night I made the slave server. Yesterday I was analyzing the log files and I found the following messages.

2013-05-21 15:13:48 BRT [30686]: [25-1] user=,db= WARNING: page 136714 of relation base/79251/79262 is uninitialized
2013-05-21 15:13:48 BRT [30686]: [26-1] user=,db= CONTEXT: xlog redo visible: rel 1663/79251/79262; blk 136714
2013-05-21 15:13:48 BRT [30686]: [27-1] user=,db= PANIC: WAL contains references to invalid pages
2013-05-21 15:13:48 BRT [30686]: [28-1] user=,db= CONTEXT: xlog redo visible: rel 1663/79251/79262; blk 136714
2013-05-21 15:13:49 BRT [30684]: [2-1] user=,db= LOG: startup process (PID 30686) was terminated by signal 6: Aborted
2013-05-21 15:13:49 BRT [30684]: [3-1] user=,db= LOG: terminating any other active server processes

It's the same problem, but now is in another table.

According the documentation: http://www.postgresql.org/docs/9.2/interactive/release-9-2-3.html

Fix multiple problems in detection of when a consistent database state has been reached during WAL replay (Fujii Masao, Heikki Linnakangas, Simon Riggs, Andres Freund)

Fix detection of end-of-backup point when no actual redo work is required (Heikki Linnakangas)

This mistake could result in incorrect "WAL ends before end of online backup" errors.

I believe that my problem is described here. What do you think about it?

On Thu, May 16, 2013 at 11:14 PM, JotaComm <jota.comm <at> gmail.com> wrote:

Hello, Fabrízio

2013/5/16 Fabrízio de Royes Mello <fabriziomello <at> gmail.com>

On Thu, May 16, 2013 at 11:12 AM, JotaComm <jota.comm <at> gmail.com> wrote:

[...]

Yesterday I identified the following messages in my log file (slave):

user=,db= WARNING: page 6629 of relation base/20449/24818 is uninitialized
user=,db= CONTEXT: xlog redo vacuum: rel 1663/20449/24818; blk 6631, lastBlockVacuumed 6626
user=,db= PANIC: WAL contains references to invalid pages
user=,db= CONTEXT: xlog redo vacuum: rel 1663/20449/24818; blk 6631, lastBlockVacuumed 6626
user=,db= LOG: startup process (PID 26293) was terminated by signal 6: Aborted
user=,db= LOG: terminating any other active server processes

Information:

PostgreSQL 9.2.3 (master and slave)
Operational System: CentOS release 6.3 (Final)
The parameter full_page_writes is enabled in both servers.

Analyzing the objects in my cluster (master) I identified the database [20449] and the relation [24818]. The relation 24818 is an index, so I ran the command REINDEX to try solving the problem. Immediately after, I tried to up the slave but I received the same errors.

user=,db= WARNING: page 6629 of relation base/20449/24818 is uninitialized
user=,db= CONTEXT: xlog redo vacuum: rel 1663/20449/24818; blk 6631, lastBlockVacuumed 6626
user=,db= PANIC: WAL contains references to invalid pages
user=,db= CONTEXT: xlog redo vacuum: rel 1663/20449/24818; blk 6631, lastBlockVacuumed 6626
user=,db= LOG: startup process (PID 26293) was terminated by signal 6: Aborted
user=,db= LOG: terminating any other active server processes

As the problem is in the wal file, so the process (above) doesn't work according my wish.

Any idea?

Hi JotaComm,

IMHO as it is your slave you could just rebuild it.

However if you want to make an attempt to recover you can do:

1) make a physical backup of this cluster

2) in your postgresql.conf set 'zero_damaged_pages = on' [1]

3) start your cluster

I really don't know if it will work, but you can try...

Thanks for your suggestion :)

I tried it and I had the same errors. I believe that will be necessary to rebuild the cluster, because the problem is in the wal file.

Regards,

[1] http://www.postgresql.org/docs/9.2/static/runtime-config-developer.html#GUC-ZERO-DAMAGED-PAGES

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Regards

--
JotaComm
http://jotacomm.wordpress.com

Thanks a lot

Regards

--
JotaComm
http://jotacomm.wordpress.com

Thank you

Regards

--
JotaComm
http://jotacomm.wordpress.com

headers

高健 | 24 May 2013 04:50

There are many processes created and died frequently during PostgreSQl idle

Hello all:

I found that during postgresql running, there are so many processes being created and then died.

I am interested in the reason.

Here is the detail:

I installed from postgresql-9.2.1.tar.bz2.

I put some debug code in fd.c 's PathNameOpenFile function:

fprintf(stderr,"+++While Calling PathNameOpenFile pid=%d.....",getpid();

fprintf(stderr,"VfdCache Address is: %p \n\n",VfdCache);

When it run, I open two termianal using psql(pid=22109 and pid=22114), then I can got information like this:

[postgres <at> lex pgsql]$ ./bin/pg_ctl -D ./data start

server starting

[postgres <at> lex pgsql]$ LOG: database system was shut down at 2013-05-24 09:28:59 CST

LOG: autovacuum launcher started

+++While Calling PathNameOpenFile pid=22106.....VfdCache Address is: 0xbfb3320

LOG: database system is ready to accept connections

+++While Calling PathNameOpenFile pid=22109.....VfdCache Address is: 0xbfb2de0

+++While Calling PathNameOpenFile pid=22114.....VfdCache Address is: 0xbfba400

+++While Calling PathNameOpenFile pid=22120.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22127.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22134.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22142.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22149.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22156.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22165.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22171.....VfdCache Address is: 0xbfe6700

+++While Calling PathNameOpenFile pid=22178.....VfdCache Address is: 0xbfe6700

But when I grep process information using ps command, I can't find process such like 22120,22127,22134,22142,22149,22156,22178.

And the output is growing longer and longer...

[root <at> lex ~]# ps -ef | grep post

root 4641 4621 0 08:28 pts/3 00:00:00 su - postgres

postgres 4642 4641 0 08:28 pts/3 00:00:00 -bash

root 4709 4688 0 08:28 pts/4 00:00:00 su - postgres

postgres 4710 4709 0 08:28 pts/4 00:00:00 -bash

root 4772 4751 0 08:29 pts/5 00:00:00 su - postgres

postgres 4773 4772 0 08:29 pts/5 00:00:00 -bash

postgres 22101 1 0 09:45 pts/3 00:00:00 /usr/local/pgsql/bin/postgres -D ./data

postgres 22103 22101 0 09:45 ? 00:00:00 postgres: checkpointer process

postgres 22104 22101 0 09:45 ? 00:00:00 postgres: writer process

postgres 22105 22101 0 09:45 ? 00:00:00 postgres: wal writer process

postgres 22106 22101 0 09:45 ? 00:00:00 postgres: autovacuum launcher process

postgres 22107 22101 0 09:45 ? 00:00:00 postgres: stats collector process

postgres 22108 4710 0 09:45 pts/4 00:00:00 ./psql

postgres 22109 22101 0 09:45 ? 00:00:00 postgres: postgres postgres [local] idle

postgres 22113 4773 0 09:46 pts/5 00:00:00 ./psql

postgres 22114 22101 0 09:46 ? 00:00:00 postgres: postgres postgres [local] idle

root 22187 6429 0 09:55 pts/6 00:00:00 grep post

[root <at> lex ~]#

So I think that some process is created and then quickly died . But would somebody can kindly say what are the purpose of those process?

How about using one worker process and keep it be alive?

headers

Nik Tek | 23 May 2013 23:37

Find all the the SELECT statements that occured

Hi,

I have a question on how to find all the SELECT statements that have occurred in the database. I don't want any DML(Insert/Update/Delete) statements to be captured. This is for knowing how many selects statements occur within the database in an 1 hour interval or on a average.

Would this simple or possible in postgres?

Thank you

Nik

headers

Oscar Calderon | 23 May 2013 23:36

Success stories of PostgreSQL implementations in different companies

Hi, this question isn't technical, but is very important for me to know. Currently, here in El Salvador our company brings PostgreSQL support, but Oracle and SQL Server are more popular here.

Even with that, some clients are being encouraged to change to PostgreSQL to lower their companies costs in technologies, but very often they ask if there are success stories of PostgreSQL implementations in companies in our region or around the world, success stories (if is possible) with some information like number of concurrent users, some hardware specs or storage size.

I think that in my country is more common to hear success stories like that about other databases like Oracle because is more expanded here, but i would like if there's a place or if you can share with me some real experiences or success stories that you ever heard of successful implementations of PostgreSQL in companies to talk with people when they ask that kind of things.

Regards.

***************************
Oscar Calderon
Analista de Sistemas
Soluciones Aplicativas S.A. de C.V.
www.solucionesaplicativas.com
Cel. (503) 7741 7850

headers

Melvin Call | 23 May 2013 23:01

New role, no database

Hello,

I have a question for which I am unable to find an answer in the
documentation, if y'all don't mind, I will ask here and hope for an
answer.

After installing PostgreSQL and logging in as the admin user of
postgres, I have created a new user with the CREATEDB privilege. How
does that user log in for the first time to create a DB? Am I required
to create a default user DB when I create the user, or is there a way
to log in with no default DB?

TIA,
Melvin

--

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

headers

Pascal Tufenkji | 23 May 2013 09:24

Temp tables

Hi,

To enhance the performance of the queries, I use temp tables in my website reports.

It seems that a temp table has not been dropped automatically in a certain session, and now the report is giving an error since the temp table already exists ?!

When I type the following, the database gives me an error:

dragon=# CREATE TEMP TABLE _parcours (id int);

ERROR: type "_parcours" already exists

1. How can I identify the session in which the temp table is still locked, so I can drop it manually

2. Why does those cases happen and the temp tables don’t drop automatically

I’d appreciate a quick reply

Thanks in advance

Pascal TUFENKJI
Service de Technologie de l'Information
Université Saint-Joseph - Rectorat
Tel: +961 1 421 132
Email: ptufenkji <at> usj.edu.lb

headers

Keith Fiske | 23 May 2013 16:15

Foreign Key violated

Client reported an issue where it appears a foreign key has been violated

prod=#\d rma_items
[snip]
rma_items_rma_id_status_fk" FOREIGN KEY (rma_id, rma_status) REFERENCES rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE

prod=# select i.rma_id, i.rma_status, r.id, r.status from rmas r join rma_items i on i.rma_id = r.id and i.rma_status != r.status;
rma_id | rma_status | id | status
------------+------------+------------+--------
1008122437 | r | 1008122437 | c
(1 row)

Attempting to reinsert this data again causes a violation error, so it doesn't appear to be broken

prod=# begin;
BEGIN
prod=# insert into rma_items (rma_id, order_item_id, return_reason_id, rma_status) values (1008122437, 1007674099, 9797623, 'r');
ERROR: insert or update on table "rma_items" violates foreign key constraint "rma_items_rma_id_status_fk"
DETAIL: Key (rma_id, rma_status)=(1008122437, r) is not present in table "rmas".
prod=# rollback;
ROLLBACK

This is running 9.2.4 on CentOS. If anyone can suggest how I can look into this deeper and find what the problem may be, I'd appreciate it. I'm here at PGCon if anyone is available to help IRL as well

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

headers

Rob Richardson | 23 May 2013 14:58

What is a DO block for?

Greetings!

Another post on this list suggested using a DO block if the user's Postgres version is 9.0 or later.  The
documentation for the DO block says what it is, but not what it is for.  The only benefit I could see for it is
allowing the use of locally defined variables.  I'm sure there's more to it than that.  What justifies the
existence of the DO block?

The message that mentioned the DO block is quoted below as an example.

Thanks very much!

RobR

-----Original Message-----
From: pgsql-general-owner <at> postgresql.org [mailto:pgsql-general-owner <at> postgresql.org] On Behalf
Of Sergey Konoplev
Sent: Thursday, May 23, 2013 2:14 AM
To: Sajeev Mayandi
Cc: pgsql-general <at> postgresql.org
Subject: Re: [GENERAL] Rule for all the tables in a schema

On Wed, May 22, 2013 at 10:34 PM, Sajeev Mayandi <Sajeev_Mayandi <at> symantec.com> wrote:
> Is there a way, I can say create a rule for all the tables in an schema?
> This will avoid writing complicated functions.

You can use DO block if your postgres version is >=9.0.

DO $$
DECLARE _tablename text
BEGIN
    FOR
        SELECT INTO _tablename tablename
        FROM pg_tables WHERE schemaname = 'schemaname'
    LOOP
        EXECUTE 'CREATE RULE ... TO $1 ...' USING _tablename;
    END LOOP;
END $$;

For <9.0 you can use shell script with psql to do the same.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru <at> gmail.com

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

--

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

headers

Wojciech Skaba | 23 May 2013 11:40

update ARRAY of COMPOSITE TYPE of text

I did:

CREATE TYPE telephone AS (
area text,
number text,
ext text
);

Then:

CREATE TABLE directory (
id integer,
tel telephone,
faxes telephone[]
);

After some data has been entered, I tried:

UPDATE directory SET tel = ROW('11', '2222222', '333') WHERE id = 1;
UPDATE directory SET faxes[1] = ROW('11', '2222222', '333') WHERE id = 1;

both worked fine, but:

UPDATE directory SET faxes = ARRAY[ROW('11', '2222222', '333'), ROW('44', '555', '666')] WHERE id = 1;

has failed: (You will need to rewrite or cast the expression) with arrow pointing to ARRAY.

Does anybody know how to overcome it?

Please note, I'm not seeking an alternative, as I can still do the following:

UPDATE directory SET faxes = '{(11\,2222222\,333),(44\,555\,666)}' WHERE id=1;

I would like, however, do the same with ARRAY/ROW.

W

--

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

headers

TJ | 23 May 2013 09:55

Re: [pgeu-general] Replication failover


We have a few different sets of servers with different versions.
9.0.4
9.1.4
9.2.3

I recently tried to  fail-over a set of 9.2.3 servers and server4 did 
notice the timeline change but did not start following it.
We do not have the recovery_target_timeline set in the recovery.conf

ATM we are not using WAL archiving only streaming.

And server4 is behind server3.

So would we need to setup WAL archiving to achieve this?

- TJ

On 22/05/13 23:22, Heikki Linnakangas wrote:
> On 22.05.2013 10:23, TJ wrote:
>> I am looking to migrate my databases from one set of hardware to another
>> all the servers are running PGSQL 9.x
>
> Which version exactly? There are differences in this area between 9.0, 
> 9.1, and 9.2. If you can choose, use the latest 9.2.X version.
>
>> The problem i have is that when i fail over to server3 using the trigger
>> file it will increment the timeline which will stop the replication of
>> server4.
>
> With 9.1 and above, it will notice the new timeline and start 
> following it, if you set up a WAL archive in addition to streaming 
> replication and set recovery_target_timeline='latest' in 
> recovery.conf. Starting with 9.3, a standby can follow a timeline 
> changes over streaming replication too, so in 9.3 it should just work.
>
> That's all assuming that server4 is behind server3; if it has already 
> replayed WAL beyond the point in the WAL where server3 switched to a 
> new timeline, it can't follow that timeline switch.
>
> - Heikki
>
>

--

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

647	May 2013
782	April 2013
633	March 2013
826	February 2013
864	January 2013
834	December 2012
964	November 2012
850	October 2012
951	September 2012
822	August 2012
642	July 2012
734	June 2012
757	May 2012
676	April 2012
977	March 2012
804	February 2012
811	January 2012
908	December 2011
1140	November 2011
1062	October 2011
1104	September 2011
1023	August 2011
865	July 2011
923	June 2011
977	May 2011
1030	April 2011
1067	March 2011
989	February 2011
871	January 2011
1043	December 2010
1200	November 2010
1130	October 2010
1108	September 2010
869	August 2010
1009	July 2010
1325	June 2010
1127	May 2010
1140	April 2010
1179	March 2010
1158	February 2010
1339	January 2010
1152	December 2009
1216	November 2009
1197	October 2009
1221	September 2009
1243	August 2009
1433	July 2009
1404	June 2009
1185	May 2009
1192	April 2009
1360	March 2009
1330	February 2009
1171	January 2009
952	December 2008
1408	November 2008
1473	October 2008
1230	September 2008
1177	August 2008
1228	July 2008
1046	June 2008
1126	May 2008
1426	April 2008
1658	March 2008
1499	February 2008
1578	January 2008
1466	December 2007
1747	November 2007
1679	October 2007
1367	September 2007
1863	August 2007
1225	July 2007
1752	June 2007
1592	May 2007
1262	April 2007
1696	March 2007
1706	February 2007
1911	January 2007
1575	December 2006
1847	November 2006
1573	October 2006
1272	September 2006
1474	August 2006
1152	July 2006
1242	June 2006
1576	May 2006
1357	April 2006
1570	March 2006
1202	February 2006
1705	January 2006
1383	December 2005
1398	November 2005
1900	October 2005
1207	September 2005
813	August 2005
1306	July 2005
1383	June 2005
1350	May 2005
1409	April 2005
1613	March 2005
1496	February 2005
1848	January 2005
1333	December 2004
1508	November 2004
1544	October 2004
1338	September 2004
1628	August 2004
952	July 2004
1215	June 2004
926	May 2004
1184	April 2004
1155	March 2004
1326	February 2004
1650	January 2004
1639	December 2003
1737	November 2003
1549	October 2003
1867	September 2003
1733	August 2003
1611	July 2003
1934	June 2003
1285	May 2003
1568	April 2003
1545	March 2003
1650	February 2003
1621	January 2003
1226	December 2002
1471	November 2002
1429	October 2002
1402	September 2002
1228	August 2002
1854	July 2002
1329	June 2002
1185	May 2002
1048	April 2002
1	December 2000
6	November 2000
5	October 2000
2	September 2000
2	April 2000
1	October 1996

Mon	Tue	Wed	Thu	Fri	Sat	Sun

		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31