Reputation
20,184
Next tag badge:
95/100 score
44/20 answers
Badges
1 17 44
Newest
 Pundit
Impact
~496k people reached

1h
answered Pausing the import of an SQL file in Postgresql?
1h
revised Postgres streaming replication between hosts with different timezone
added 429 characters in body
1h
revised Postgres streaming replication between hosts with different timezone
added 429 characters in body
1h
answered Postgres streaming replication between hosts with different timezone
1d
comment How to examine PostgreSQL server's SSL certificate?
One option is to patch openssl s_client to handshake with the PostgreSQL protocol. You can probably also do it with Java, by passing a custom SSLSocketFactory to PgJDBC. I'm not sure there are any simple options.
2d
comment Postgres - Error while adding constraints
Read and act on wiki.postgresql.org/wiki/Corruption now. Hopefully it's just a damaged index, but that should not happen either. Looks like a file is truncated.
2d
comment How to have remote standby PostgreSQL server in different location (AWS)?
@n8gard Well. The error message explains the problem. The manual link i provided above has more detail
Jul
30
comment How to have remote standby PostgreSQL server in different location (AWS)?
@n8gard Correct. Hot standby does not depend on streaming replication.
Jul
30
comment How to have remote standby PostgreSQL server in different location (AWS)?
@n8gard Correct. You can optionally have the standby connect to the master too, and this will reduce replication lag but is not required.
Jul
30
awarded  Pundit
Jul
30
comment Clobbered default database in postgreSQL
Also, if you've somehow managed to drop all databases (which shouldn't be possible) you can still start PostgreSQL up in single-user recovery mode against template0.
Jul
30
answered Intentionally corrupting an index in PostgreSQL
Jul
30
comment Intentionally corrupting an index in PostgreSQL
Please post a new question and link to this one for reference/context.
Jul
30
comment Intentionally corrupting an index in PostgreSQL
I disagree with your characterisation of indisvalid = f indexes as corrupt. They are not. They are marked unusable and may be in the process of being built, etc, but this is a normal state. PostgreSQL never "detects" a corrupt index and unsets indisvalid, that's not what the field is for. Sure, it's useful to play with this, but it has almost nothing to do with the original issue or the follow up question.
Jul
30
comment postgres9.4 streaming replication not streaming
Please show more of the logs from the replica, like tail -n 50 or so
Jul
30
comment Can jsonb do fast search by *value* or even a partial value? Other types?
You'll probably want to maintain a generated column for text search purposes that contains a "flattened" representation of the data. Since you want partial matches you can't use full text search, either; a search for "burg" won't find "hamburger" in fts, it can only do whole words with stemming, and prefix matches. Look at the pg_trgm index support for infix searches. Be aware it's slow.
Jul
30
revised Can jsonb do fast search by *value* or even a partial value? Other types?
edited title
Jul
30
answered How to determine if pg_dumpall is still running
Jul
30
comment How to have remote standby PostgreSQL server in different location (AWS)?
My answer is quite general, as you omitted key details from your question like the actual configuration you're using. Post a new question with a detailed, specific information on how you configured things, relevant config files and settings changed, etc, if you want specific help on your configuration. Include log file excerpts too.
Jul
30
answered How to have remote standby PostgreSQL server in different location (AWS)?