0

I try backup and restore using WAL-E/WAL-G.

I can backup and restore.

But i want check recovery_target_time.

During recovery, by default WAL-E will attempt to pull and restore all WAL segments available. If you wish to restore to a specific POINT IN TIME than specify.

# restore to 4:38PM on 3/6/2012
recovery_target_time = '2012-03-06 16:38:00'

I think need generate value every minute long time and Insert into table.

Found insert events with CURRENT_TIMESTAMP

INSERT INTO events (ts,description) VALUES (CURRENT_TIMESTAMP,'disc full');

Found insert SELECT NOW()

INSERT INTO indexing_table(created_at) SELECT NOW() FROM generate_series(1,100);

This is no answer :(

How build table?

         created_at           
-------------------------------
2019-02-13 14:16:20.799219+03
2019-02-13 14:17:20.799219+03
2019-02-13 14:18:20.799219+03
2019-02-13 14:19:20.799219+03

or

    created_at           
-------------------
2019-02-13 14:16:20
2019-02-13 14:17:20
2019-02-13 14:18:20
2019-02-13 14:19:20

Need generate row with different minutes.

Any help would be appreciated in creating these tables! Thanks!

  • Another option to generate WAL would be to use pgbench and let it run for a long time. – a_horse_with_no_name Feb 13 at 12:24
  • Not Generate WAL. Need generate row with different minutes. – Anton Patsev Feb 13 at 12:34
1

I am not entirely sure what your question is, but if you are confused that all rows have the same timestamp value, this is because now() and current_timestamp return the time at the start of the transaction.

Quote from the manual

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp

And then:

clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.

So if I understood your question correctly, you probably want to use clock_timestamp() rather than now()


Another option is to use generate_series() to generate timestamps:

INSERT INTO indexing_table(created_at) 
SELECT *
FROM generate_series(timestamp '2019-01-01 00:00:00', 
                     timestamp '2019-03-31 00:00:00', 
                     interval '1 minute') ;
  • Thanks! I added this code to cron: */1 * * * * postgres psql -U postgres -d test1 -c "INSERT INTO indexing_table(created_at) VALUES (CURRENT_TIMESTAMP);" – Anton Patsev Feb 14 at 4:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.