Re: recovery.conf not getting changed to recovery.done after PITR

Поиск
Список
Период
Сортировка
От amit tripathi
Тема Re: recovery.conf not getting changed to recovery.done after PITR
Дата
Msg-id CABpqzULXiHhOs0+0711SjwOfMZiUku1tqk42y_MsW3U1kj0ESQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: recovery.conf not getting changed to recovery.done after PITR  (Jerry Sievers <gsievers19@comcast.net>)
Ответы Re: recovery.conf not getting changed to recovery.done after PITR  (Rajni Baliyan <saan654@gmail.com>)
Список pgsql-admin
Hi Jerry,

Thanks for responding.

I am using following two parameters in recovery.conf

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2018-09-07 03:25:46'

postgresql.conf has following configurations. Other options are commented out.
listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'
log_destination = 'stderr'
logging_collector =on
log_timezone = 'US/Eastern'
datestyle = 'iso, mdy'
timezone ='US/Eastern'
lc_messages = 'en_US.UTF-8'
lc_monetary ='en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
Adding the logs details as well.

2018-09-07 03:35:57.745 EDT [8264] LOG:  database system was interrupted; last known up at 2018-09-07 03:23:31 EDT
2018-09-07 03:35:59.593 EDT [8264] LOG:  starting point-in-time recovery to 2018-09-07 03:25:46-04
2018-09-07 03:35:59.682 EDT [8264] LOG:  restored log file "000000010000000000000003" from archive
2018-09-07 03:35:59.722 EDT [8264] LOG:  redo starts at 0/3000028
2018-09-07 03:35:59.725 EDT [8264] LOG:  consistent recovery state reached at 0/3000130
2018-09-07 03:35:59.725 EDT [8262] LOG:  database system is ready to accept read only connections
2018-09-07 03:36:00.058 EDT [8264] LOG:  restored log file "000000010000000000000004" from archive
2018-09-07 03:36:00.097 EDT [8264] LOG:  recovery stopping before commit of transaction 562, time 2018-09-07 03:26:17.435255-04
2018-09-07 03:36:00.097 EDT [8264] LOG:  recovery has paused
2018-09-07 03:36:00.097 EDT [8264] HINT:  Execute pg_wal_replay_resume() to continue.
2018-09-07 03:36:54.138 EDT [8288] ERROR:  cannot execute CREATE TABLE in a read-only transaction
2018-09-07 03:36:54.138 EDT [8288] STATEMENT:  CREATE TABLE scale_data5 ( section NUMERIC NOT NULL, id1 NUMERIC NOT NULL, id2 NUMERIC NOT NULL );
Regards,
Amit

On Thu, Sep 6, 2018 at 9:03 PM Jerry Sievers <gsievers19@comcast.net> wrote:
amit tripathi <amittripathi1445@gmail.com> writes:

> Hi All,
>
> Need help on below issue.
>
>>In recovery.conf file if we give recovery_target_time as  2018-09-05
> 09:54:21 (without EST)  and restart the postgresql, recovery.conf is
> not getting changed to recovery.done. Although PITR is successful and
> only till given time records/tables are restored. But database goes
> in recovery mode and i am unable to enter more records.

Show us your recovery.conf after masking anything sensitive.

You probably are paused at a recovery target and if trying to promote
the pitr cluster at this point, need to un-pause replication  else it
will not respond to the promote.

HTH

>
>>In the second case if i add EST and restart the postgresql,
> recovery.conf is getting changed to recovery.done but everything is
> getting restored. I mean tables/records added after 2018-09-05
> 09:54:21 are also restored.
>
> Other information-Doing it on Linux host. Timezone in postgresql.conf
> is US/Eastern.
>
>
> Regards,
> Amit
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

В списке pgsql-admin по дате отправления:

Предыдущее
От: "For your eyes only..."
Дата:
Сообщение: 答复: Determine which query prevents applying WALs on standby
Следующее
От: Rajni Baliyan
Дата:
Сообщение: Re: recovery.conf not getting changed to recovery.done after PITR