Postgresql – Insert into value as generate row with different minutes

postgresql

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

    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!

Best Answer

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') ;

Related Solutions

PostgreSQL on AWS – How to Get WAL Files from RDS Instance

Update: I've posted about this to the AWS forums - please go chime in and ask for it there.

At time of writing, Amazon RDS does not support physical replication outside RDS. You can GRANT users the REPLICATION right using an rds_superuser login, but you can't configure replication entries for outside IPs in pg_hba.conf.

Furthermore, when you create a DB parameter group in RDS, some key parameters are shown but locked, e.g. archive_command, which is locked to /etc/rds/dbbin/pgscripts/rds_wal_archive %p. AWS RDS for PostgreSQL does not appear to expose these WALs for external access (say, via S3) as it would need to if you were to use WAL-shipping replication for external PITR.

So at this point, if you want wal-shipping, don't use RDS. It's a canned easy-to-use database, but easy-to-use often means that it's also limited, and that's certainly the case here. As Joe Love points out in the comments, it provides WAL shipping and PITR within RDS, but you can't get access to the WAL to that from outside RDS.

So you need to use RDS's own backup facilities - dumps, snapshots and its own WAL-based PITR.

Even if RDS did let you make replication connections (for pg_basebackup or streaming replication) and allowed you to access archived WAL, you might not be able to actually consume that WAL. RDS runs a patched PostgreSQL, though nobody knows how heavily patched or whether it significantly alters the on-disk format. It also runs on the architecture selected by Amazon, which is probably x64 Linux, but not easily determined. Since PostgreSQL's on disk format and replication are architecture dependent, you could only replicate to hosts with the same architecture as that used by Amazon RDS, and only if your PostgreSQL build was compatible with theirs.

Among other things this means that you don't have any easy way to migrate away from RDS. You'd have to stop all writes to the database for long enough to take a pg_dump, restore it, and get the new DB running. The usual tricks with replication and failover, with rsync, etc, won't work because you don't have direct access to the DB host.

Even if RDS ran an unpatched PostgreSQL Amazon probably wouldn't want to permit you to do WAL streaming into RDS or import into RDS using pg_basebackup for security reasons. PostgreSQL treats the data directory as trusted content, and if you've crafted any clever 'LANGUAGE c' functions that hook internal functionality or done anything else tricky you might be able to exploit the server to get greater access than you're supposed to have. So Amazon aren't going to permit inbound WAL anytime soon.

They could support outbound WAL sending, but the above issues with format compatibility, freedom to make changes, etc still apply.

Instead you should use a tool like Londiste or Bucardo.

Postgresql – Select where value in row is different and update

update cartographic_text_vm 
set primary_key = new.primary_key 
from 
    (select *  
     from cartographic_text_vm 
     where (fid, ckpt) in 
         (select fid, min(ckpt) 
          from cartographic_text_vm 
          group by fid)
    ) new 
where cartographic_text_vm.fid = new.fid 
  and cartographic_text_vm.primary_key != new.primary_key;

Best Answer

Related Solutions

PostgreSQL on AWS – How to Get WAL Files from RDS Instance

Postgresql – Select where value in row is different and update

Related Question