Reputation
8,155
Top tag
Next privilege 10,000 Rep.
Access moderator tools
Badges
3 18 43
Newest
 Informed
Impact
~372k people reached

May
4
comment Make a backup file or save a DB in PostgreSQL
It mostly depends on which user has access to which table, either directly (as a result from some earlier GRANT commands, for example) or through roles assigned to them. It's basically very easy to test it yourself: create the dumps with the different users and compare them (directly or restoring the database under different names).
May
2
comment Can I export a PostgreSQL database from Heroku and Import as a PostGIS database in Digital Ocean?
I thing nothing can stop you from taking a dump and restore it elsewhere... See devcenter.heroku.com/articles/heroku-postgres-import-export
Apr
30
comment Updating databases that user's can manipulate
One possible way is adding a is_deleted tinyint column, with 0 as default. Instead of deleting, you can set it to 1, and then filter these out in the queries. This way you always have all countries that ever got into the table.
Apr
30
comment Heroku Postgres Follower
Just a few rows above the anchor you linked: 'A database follower is a read-only copy of the master database that stays up-to-date with the master database data.' You simply cannot change there anything.
Apr
30
comment PostgreSQL 9.0 live migration/replication with native/bucardo between multiple architectures
As @a_horse_with_no_name stated, you cannot do this with 'normal' replication. Setting up and testing a trigger-based logical replication would take possibly much longer than a dump-and-restore, and you still have to move the untouched data somehow (combining these two approaches, for example). On an other hand, basically all systems need some maintenance windows (for OS upgrades, for example), I'd try no negotiate one.
Apr
29
comment Was using `set datcollate='C'` a mistake?
In this case, you'll have to create a new cluster and so on (unless you are sure that the current DB is not corrupted somehow).
Apr
29
comment Was using `set datcollate='C'` a mistake?
Can you directly connect to the database with some sufficiently powerful user? If yes, you still can fix the collation - I'll edit the answer with it.
Apr
28
comment Restoring Postgres fixtures into a different database with different premissions
An alternative approach could be just mapping the users between the two DBs. Say, you have role_a in DB a, role_b in DB b, both of them having to act the same in their respective databases. You could create role_a in DB b, granting role_a to it, and there is no need to manipulating the dump.
Apr
28
comment Postgres Slow Queries - Autovacuum frequency
How fast does your data change?
Apr
27
comment Column Datatype Conversion Question
So you want to remove the time part? Anyway, you should look at the to_date() function.
Apr
27
comment how to use index to speed up sorting in postgres
At a first glance, the ORDER BY in the subquery is totally useless. Furthermore, the linked plan cannot be a result of the posted query - there is no mention of metadata, for example.
Apr
27
comment Install PostGIS v2 for PostgreSQL v9.2
The links are subject to link rot, please consider adding the important substeps from them to your answer. Thanks in advance!
Apr
24
comment How to catch code of error which occured inside plv8 function?
Writing to Andrew about this could be possibly a good idea, you will find his email address at pgxn.org/user/andrew
Apr
24
comment How to catch code of error which occured inside plv8 function?
It's not clear if you do some error handling in your function and what exactly 'pg client' is.
Apr
23
comment Is it possible for a client to kill a long-running Postgres query?
It is possible to send a ctrl+c or similar from the client that runs the query. Otherwise, it is only possible to kill it with pg_terminate_backend(), that needs superuser rights. Or it can be sent a SIGTERM from the OS, if you have access there.
Apr
23
comment Speed up procedure
Could you maybe post the table definitions and the execution plan, too?
Apr
23
comment PostgreSQL: changing password for a user is not working
This is the default.
Apr
23
comment What to do with WAL files for Postgres Slave reset
I think the cleanest and safest way is just to rebuild the slave completely. pg_start_backup() is not supposed to lock the DB, except when you pass fast = TRUE to it - this latter will cause a slowdown of any concurrently executing queries. See postgresql.org/docs/9.4/static/…
Apr
22
comment Anything like a hash for Postgresql databases?
stackoverflow.com/questions/2178907/…
Apr
22
comment Anything like a hash for Postgresql databases?
But unfortunately there is no guarantee that it produces the dump in the same order for two tries...