[GENERAL] Can't Bringing the former Primary up as a Standby
Hello! I got: root@reactor:~# invoke-rc.d postgresql start Starting PostgreSQL 9.1 database server: mainThe PostgreSQL server failed to start. Please check the log output: 2011-08-11 12:12:42 EEST LOG: database system was interrupted; last known up at 2011-08-11 12:04:21 EEST 2011-08-11 12:12:42 EEST LOG: could not open file "pg_xlog/0001004A" (log file 0, segment 74): No such file or directory 2011-08-11 12:12:42 EEST LOG: invalid checkpoint record 2011-08-11 12:12:42 EEST FATAL: could not locate requir While I do not: 1. cp recovery.done recovery.conf 2. change host to new primary at recovery.conf Is it ok? I have to do that to bring up primary up as a standby? But it doesn't help, I cant connect postgresql, last log: 2011-08-11 12:46:02 EEST LOG: shutting down 2011-08-11 12:46:02 EEST LOG: restartpoint starting: shutdown immediate 2011-08-11 12:46:02 EEST LOG: restartpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.000 s, total=0.029 s; sync files=0, longest=0.000 s, average=0.000 s 2011-08-11 12:46:02 EEST LOG: recovery restart point at 0/5320 2011-08-11 12:46:02 EEST LOG: database system is shut down 2011-08-11 12:46:18 EEST LOG: database system was shut down in recovery at 2011-08-11 12:46:02 EEST 2011-08-11 12:46:18 EEST LOG: entering standby mode 2011-08-11 12:46:18 EEST LOG: consistent recovery state reached at 0/5378 2011-08-11 12:46:18 EEST LOG: record with zero length at 0/5378 2011-08-11 12:46:18 EEST LOG: streaming replication successfully connected to primary 2011-08-11 12:46:18 EEST LOG: connection received: host=[local] 2011-08-11 12:46:18 EEST LOG: incomplete startup packet 2011-08-11 12:46:19 EEST LOG: connection received: host=[local] 2011-08-11 12:46:19 EEST FATAL: the database system is starting up 2011-08-11 12:46:19 EEST LOG: connection received: host=[local] 2011-08-11 12:46:19 EEST FATAL: the database system is starting up 2011-08-11 12:46:20 EEST LOG: connection received: host=[local] 2011-08-11 12:46:20 EEST FATAL: the database system is starting up 2011-08-11 12:46:20 EEST LOG: connection received: host=[local] 2011-08-11 12:46:20 EEST FATAL: the database system is starting up 2011-08-11 12:46:21 EEST LOG: connection received: host=[local] 2011-08-11 12:46:21 EEST FATAL: the database system is starting up 2011-08-11 12:46:21 EEST LOG: connection received: host=[local] 2011-08-11 12:46:21 EEST FATAL: the database system is starting up 2011-08-11 12:46:22 EEST LOG: connection received: host=[local] 2011-08-11 12:46:22 EEST FATAL: the database system is starting up 2011-08-11 12:46:22 EEST LOG: connection received: host=[local] 2011-08-11 12:46:22 EEST FATAL: the database system is starting up 2011-08-11 12:46:23 EEST LOG: connection received: host=[local] 2011-08-11 12:46:23 EEST FATAL: the database system is starting up 2011-08-11 12:46:23 EEST LOG: connection received: host=[local] 2011-08-11 12:46:23 EEST FATAL: the database system is starting up 2011-08-11 12:46:24 EEST LOG: connection received: host=[local] 2011-08-11 12:46:24 EEST FATAL: the database system is starting up 2011-08-11 12:46:24 EEST LOG: connection received: host=[local] 2011-08-11 12:46:24 EEST LOG: incomplete startup packet processes: postgres 18696 1.2 1.0 926428 40688 ?S12:54 0:00 /usr/ lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf postgres 18697 0.0 0.0 926896 1832 ?Ss 12:54 0:00 postgres: startup process waiting for 00010053 postgres 18698 0.0 0.0 926832 1812 ?Ss 12:54 0:00 postgres: writer process postgres 18699 0.0 0.0 937440 2848 ?Ss 12:54 0:00 postgres: wal receiver process streaming 0/5378 All it heppens after: postgres@reactor:~$ repmgr -D /var/lib/postgresql/9.1/main -d pgbench - p 5432 -U eps -R postgres --verbose --force standby clone 10.0.1.123 Opening configuration file: ./repmgr.conf repmgr: directory "/var/lib/postgresql/9.1/main" exists but is not empty repmgr connecting to master database repmgr connected to master, checking its state Succesfully connected to primary. Current installation size is 182 MB Starting backup... standby clone: master control file '/media/postgresql/9.1/data/global/ pg_control' rsync command line: 'rsync --archive --checksum --compress --progress --rsh=ssh --delete postg...@10.0.1.123:/media/postgresql/9.1/data/ global/pg_control /var/lib/postgresql/9.1/main/global/.' receiving incremental file list pg_control 8192 100%7.81MB/s0:00:00 (xfer#1, to-check=0/1) sent 102 bytes received 234 bytes 672.00 bytes/sec total size is 8192 speedup is 24.38 standby clone: master data directory '/media/postgresql/9.1/data' rsync command line: 'rsync --archive --checksum --compress --progress --rsh=ssh --delete --exclude=pg_xlog* --exclude=pg_control -- exclude=*
Re: [GENERAL] need help with query, how to fold select result to array?
CREATE TYPE varchararray AS (f varchar[]);
SELECT office_id, serial, commit_date, service_id,
array_agg(DISTINCT ROW(ARRAY(SELECT meter_id::varchar UNION ALL
SELECT organization_reading::varchar UNION ALL
SELECT reading::varchar))::varchararray)
FROM meter_readings
WHERE office_id = 134 AND
serial = 27 AND
commit_date = '2010-01-11' AND
commit_time = '13:44:37' AND
person_id = 300871
GROUP BY office_id, serial, commit_date, service_id;
office_id | serial | commit_date | service_id |
array_agg
---++-++--
134 | 27 | 2010-01-11 | 2 |
{"(\"{2668722,616,0}\")","(\"{71629130,15518,0}\")"}
134 | 27 | 2010-01-11 | 4 | {"(\"{019210,372,0}\")"}
134 | 27 | 2010-01-11 | 75 | {"(\"{111029,9505,0}\")"}
(3 rows)
I want something like that, but without varchararray type;
parentheses, slashes and " in array_agg field, where does it get from?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help with query, how to fold select result to array?
but I have to fold it in multi-dimensional, array like {{71629130,
15518, 0}, {2668722, 616, 0}} ?
but it's not possible to pass more then one argument to the array_agg
function :/
2010/1/12 A. Kretschmer :
> In response to Sergey Levchenko :
>> eps=# SELECT office_id, serial, commit_date, service_id, meter_id,
>> organization_reading, reading FROM meter_readings WHERE office_id =
>> 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time =
>> '13:44:37' AND person_id = 300871;
>>
>> office_id | serial | commit_date | service_id | meter_id |
>> organization_reading | reading
>> ---++-++--+--+-
>> 134 | 27 | 2010-01-11 | 2 | 71629130 |
>> 15518 | 0
>> 134 | 27 | 2010-01-11 | 2 | 2668722 |
>> 616 | 0
>> 134 | 27 | 2010-01-11 | 75 | 111029 |
>> 9505 | 0
>> 134 | 27 | 2010-01-11 | 4 | 019210 |
>> 372 | 0
>> (4 rows)
>>
>> How to get ?
>> 134 | 27 | 2010-01-11 | 2 | {{71629130, 15518,
>> 0}, {2668722, 616, 0}}
>> 134 | 27 | 2010-01-11 | 75 | {111029, 9505, 0}
>> 134 | 27 | 2010-01-11 | 4 | {019210, 372, 0}
>>
>> Thanks a lot!
>
> You can use array_agg(since 8.4):
>
> test=# create table bla (id int, value text);
> CREATE TABLE
> test=*# copy bla from stdin;
> Enter data to be copied followed by a newline.
> End with a backslash and a period on a line by itself.
>>> 1 bla
>>> 1 foo
>>> 1 bar
>>> 2 foobar
>>> 2 test
>>> \.
> test=*# select id, array_agg(value) from bla group by 1 order by 1;
> id | array_agg
> +---
> 1 | {bla,foo,bar}
> 2 | {foobar,test}
> (2 rows)
>
>
> If you don't have 8.4 search the docu for array_accum:
> http://www.postgresql.org/docs/8.4/interactive/xaggr.html
>
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] need help with query, how to fold select result to array?
eps=# SELECT office_id, serial, commit_date, service_id, meter_id,
organization_reading, reading FROM meter_readings WHERE office_id =
134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time =
'13:44:37' AND person_id = 300871;
office_id | serial | commit_date | service_id | meter_id |
organization_reading | reading
---++-++--+--+-
134 | 27 | 2010-01-11 | 2 | 71629130 |
15518 | 0
134 | 27 | 2010-01-11 | 2 | 2668722 |
616 | 0
134 | 27 | 2010-01-11 | 75 | 111029 |
9505 | 0
134 | 27 | 2010-01-11 | 4 | 019210 |
372 | 0
(4 rows)
How to get ?
134 | 27 | 2010-01-11 | 2 | {{71629130, 15518,
0}, {2668722, 616, 0}}
134 | 27 | 2010-01-11 | 75 | {111029, 9505, 0}
134 | 27 | 2010-01-11 | 4 | {019210, 372, 0}
Thanks a lot!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with SET CONSTRAINTS some_constraint_fkey DEFERRED
Why I get: update or delete on table "people" violates foreign key
constraint "transactions_person_id_fkey" on table "transactions"
But before that query I run: SET CONSTRAINTS
transactions_person_id_fkey DEFERRED; before delete from people table
Log:
2009-12-09 20:01:51 EET LOG: statement: BEGIN; SET TRANSACTION
ISOLATION LEVEL READ COMMITTED
2009-12-09 20:01:51 EET LOG: duration: 0.176 ms
2009-12-09 20:01:51 EET LOG: statement: select version()
2009-12-09 20:01:51 EET LOG: duration: 0.552 ms
2009-12-09 20:01:51 EET LOG: statement: select current_schema()
2009-12-09 20:01:51 EET LOG: duration: 0.391 ms
2009-12-09 20:01:51 EET LOG: statement: SELECT CAST('test unicode
returns' AS VARCHAR(60)) AS anon_1
2009-12-09 20:01:51 EET LOG: duration: 0.506 ms
2009-12-09 20:01:51 EET LOG: statement: SET search_path TO system,ternopil
2009-12-09 20:01:51 EET LOG: duration: 0.223 ms
2009-12-09 20:01:51 EET LOG: statement: SELECT imports.id AS
imports_id, imports.name AS imports_name, imports."table" AS
imports_table, imports.priority AS imports_priority, imports.source AS
imports_source, imports.encoding AS imports_encoding, imports.fields
AS imports_fields, imports.filter AS imports_filter, imports.disabled
AS imports_disabled
FROM imports
WHERE imports.source IN (E'people.csv') ORDER BY imports.priority
2009-12-09 20:01:51 EET LOG: duration: 1.993 ms
2009-12-09 20:01:51 EET LOG: statement: SELECT pg_constraint.conname
FROM pg_catalog.pg_constraint, pg_catalog.pg_class,
pg_catalog.pg_namespace WHERE pg_constraint.confrelid = pg_class.oid
AND pg_constraint.contype = E'f' AND pg_class.relname = E'people' AND
pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname =
E'ternopil' AND pg_constraint.conrelid != pg_class.oid
2009-12-09 20:01:51 EET LOG: duration: 3.722 ms
IT'S HERE
2009-12-09 20:01:51 EET LOG: statement: SET CONSTRAINTS
meter_readings_person_id_fkey,transactions_person_id_fkey DEFERRED
2009-12-09 20:01:51 EET LOG: duration: 0.490 ms
2009-12-09 20:01:51 EET LOG: statement: DELETE FROM people
2009-12-09 20:01:51 EET LOG: duration: 743.187 ms
2009-12-09 20:01:51 EET LOG: statement: COPY
people(id,street_id,building,corpus,appartment,block,last_name,hd,residents,space,rooms,privilege,comment)
FROM stdin WITH DELIMITER AS E' '
2009-12-09 20:02:12 EET LOG: duration: 20142.596 ms
2009-12-09 20:02:12 EET LOG: statement: COMMIT
2009-12-09 20:02:12 EET ERROR: update or delete on table "people"
violates foreign key constraint "transactions_person_id_fkey" on table
"transactions"
2009-12-09 20:02:12 EET DETAIL: Key (id)=(306216) is still referenced
from table "transactions".
2009-12-09 20:02:12 EET STATEMENT: COMMIT
eps=# \d transactions
Table "ternopil.transactions"
Column | Type |
Modifiers
++---
id | integer| not null default
nextval('transactions_id_seq'::regclass)
...
person_id | integer| not null
...
Foreign-key constraints:
...
"transactions_person_id_fkey" FOREIGN KEY (person_id) REFERENCES
people(id) DEFERRABLE
...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] partitioning question. need current month and archive partitions.
Hi All!
I have a table - transaction pool - with a lot of rows, but I use only
data for the latest month, or current year in my computations.
How can I split data to partitions like that if I can't use CHECK
constraints with non constant objects like, extract('month' from
CURRENT_DATE), extract('year' from CURRENT_DATE):
transaction_pool - master table
transaction_currentmonth - table contains transactions commited
_only_ this (current) month
transaction_currentyear - table contains all transaction for this
year except for transaction_currentmonth rows
transaction_archive - table contains everything except for
transaction_currentmonth, transaction_currentyear rows
Will it be helpful to make such a kind of partitioning?
Thanks a lot!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: "TZ"/"tz" not supported
When I execute query, I've got error message.
test=> SELECT to_timestamp('00:00:05.601 SAMST Tue Jun 28 2005',
'HH24:MI:SS.MS TZ Dy Mon DD ');
ERROR: "TZ"/"tz" not supported
How can I convert '00:00:05.601 SAMST Tue Jun 28 2005' (varchar type)
to timestamp with time zone?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] ilike, ~*, ~~* does not work on 8.0.3 (db encoding - UNICODE)
I can not get work ilike, ~*, ~~* with not ascii chars, but upper,
lower, order by - work good.
db encoding is UNICODE.
test=> SELECT 'Тест' ~~* 'тест';
?column?
--
f
test=> SELECT 'Тест'' ~* 'тест';
?column?
--
f
test=> SELECT 'Тест'' ILIKE 'тест';
?column?
--
f
test=> SELECT upper('Тест'') ~~ upper('тест');
?column?
--
t
tesscom=> SELECT upper('Тест'') ~ upper('тест');
?column?
--
t
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql unicode lower/upper problem
I modify pg_createcluster and add locale options exec $initdb, '--locale', 'ru_RU.UTF-8', '--encoding', 'UTF-8', '-D', $datadir then drop and create cluster with my locale. now lower and upper work good with unicode. P.S. it's bad that we can not modify lc_ctype and lc_collate from postgresql.conf like lc_messages, lc_monetary, ... I think that it will be good idea to add locale specific options to pg_createcluster... On Wed, 23 Mar 2005 12:17:14 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Sergey Levchenko <[EMAIL PROTECTED]> writes: > > I am not able to get work lower and upper functions on postgresql > > v8.0.1 and 8.1b(current cvs copy). I use Debian SID i686 GNU/Linux. > > Locale: ru_RU.KOI8-R > > > createdb -E UNICODE test > > I think the problem is you selected a database encoding that doesn't > match what the locale expects. You can't really mix-and-match if you > expect locale-specific stuff like upper/lower to work. For that > locale you must use -E KOI8. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] postgresql unicode lower/upper problem
I am not able to get work lower and upper functions on postgresql v8.0.1 and 8.1b(current cvs copy). I use Debian SID i686 GNU/Linux. Locale: ru_RU.KOI8-R createdb -E UNICODE test psql test test=> SET client_encoding TO KOI8; SET test=> SELECT t FROM t1; t ÐÐ tEsT (2 rows) test=> SELECT upper(t) FROM t1; upper ÐÐ TEST test=> SELECT lower(t) FROM t1; lower ÐÐ test (2 rows) How you can see it work perfect with latin and does not do any lower/upper with koi8. what I do wrong? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
