New answers tagged

0

I'm going to add to this. The problem as noted by Laurenz is when its time to clean the deleted rows there must be enough room in the TableSpace to do the vacuum, so using OS disk quota is problematic and the error messages do no reflect what is going on. I remember reading somewhere that someone on some database had implemented something similar I do ...


0

The best way to maintain a size limit for a table (or set of tables or a database) is to create a separate file system with the desired size maximum, define a tablespace on that file system and put the tables into that tablespace. It is OK to limit size for user tables in that way; exceeding the limit will cause an error, but processing will continue. But ...


1

you can set up master - slave replication using pgpool, master-master is not supported. To set up active - active replication you will need to use tool like SymmetricDS. Cheers!


0

It would be better to use FILTER (WHERE ...) and COALESCE(..., '{}'::JSON). select json_build_object( 'ID', s.page_id, 'Domain', domain, 'Sections', COALESCE(json_object_agg ( s.title, json_build_object( 'ID', s.sid, 'Texts', t.txts, 'Images', i.imgs ...


1

If your table is well-vacuumed, you can get an index only scan by creating the index: CREATE INDEX ON temp_test(dt,val); This might be quite a bit faster as it doesn't need to visit the table at all. (In my test, it was about 2.5 times faster than the sort.) As to why it chooses the wrong plan with the index you already have, it could just be that you ...


0

Using an index is only fast if you select a small set of records. You want the entire table, the DB is wasting its time switching between index and data.


0

You can try with below 1. For extract your file tar xvzf your_file.tar.gz 2. For restore psql -h localhost -d database_name -f database_file_location


3

Of course the best option would be to remember how exactly you took that backup... If you want to restore the backup on a hosted database, it had better be a logical backup created with pg_dump. So make sure it is not a physical backup: Run tar -tzvf 20200204_data_tar.gz and make sure it does not contain files like postgresql.auto.conf or PG_VERSION. Once ...


0

You can add a WHERE clause to the UPDATE part of an INSERT ... ON CONFLICT insert into the_table (sid, somecolumn) values (...) on conflict (sid) do update set somecolumn = excluded.somecolumn, changed = true where the_table.somecolumn <> excluded.somecolumn; If you need to deal with NULL values in somecolumn use where the_table....


Top 50 recent answers are included