How can I set all constraints to be deferrable for a DB/schema

Hi: psql (9.6.7, server 11.3) (linux) Is there a way to set all constraints on all tables of a DB and/or schema to be deferrable ? Or do I have to do them one-by-one ?

Re: Mixed Locales and Upgrading

On Tue, Mar 17, 2020 at 10:45:50AM -0400, Tom Lane wrote: > Don Seiler writes: >> What are the ramifications of changing collation like that? Should we >> consider rebuilding indexes ASAP after that? > > Text indexes would definitely be at risk here. I'm not really certain > how bad the problem

Re: Automatic failover

On 2020/03/17 19:30, Sonam Sharma wrote: > I have setup replication using repmgr. Wanted to know how much time > the slave node will take to become new primary ?? If any document, > can someone please share of auto failover. With automatic failover, > how much time the slave takes to become new p

Re: Fwd: PG12 autovac issues

On 3/17/20 3:48 PM, Justin King wrote: On Tue, Mar 17, 2020 at 5:39 PM Adrian Klaver wrote: On 3/17/20 3:22 PM, Justin King wrote: Apologies, I accidentally sent this to the pgsql-admin list initially but intended it go here: We have a database that isn't overly large (~20G), but gets incred

Re: Fwd: PG12 autovac issues

On Tue, Mar 17, 2020 at 5:39 PM Adrian Klaver wrote: > > On 3/17/20 3:22 PM, Justin King wrote: > > Apologies, I accidentally sent this to the pgsql-admin list initially > > but intended it go here: > > > > We have a database that isn't overly large (~20G), but gets incredibly > > frequent updates

Re: Order by and timestamp SOLVED

On 3/17/20 8:57 AM, Björn Lundin wrote: I am still not sure that this can be marked solved. I am trying to figure out how running a different version of psql once can affect another version of psql. That would seem to imply psql changed something on the server and AFAIK sorting/ordering is

Re: Fwd: PG12 autovac issues

On 3/17/20 3:22 PM, Justin King wrote: Apologies, I accidentally sent this to the pgsql-admin list initially but intended it go here: We have a database that isn't overly large (~20G), but gets incredibly frequent updates. Here's an example table: feedi=# select * from pg_stat_all_tables where

Fwd: PG12 autovac issues

Apologies, I accidentally sent this to the pgsql-admin list initially but intended it go here: We have a database that isn't overly large (~20G), but gets incredibly frequent updates. Here's an example table: feedi=# select * from pg_stat_all_tables where schemaname = 'production' and relname =

Re: Temporary tablespaces on a RAM disk

Laurenz Albe writes: > On Tue, 2020-03-17 at 19:57 +, Daniel Westermann (DWE) wrote: > >> >> is someone using temporary tablespaces on a RAM disk ? Any experiences >> >> with that? >> >> I did some quick tests and checked the archives but could not find any >> >> information that either conf

Re: Temporary tablespaces on a RAM disk

On Tue, 2020-03-17 at 19:57 +, Daniel Westermann (DWE) wrote: > >> is someone using temporary tablespaces on a RAM disk ? Any experiences > >> with that? > >> I did some quick tests and checked the archives but could not find any > >> information that either confirmed it is a bad idea nor the

Re: Temporary tablespaces on a RAM disk

Hi Laurenz, >From: Laurenz Albe >Sent: Tuesday, March 17, 2020 20:50 >To: Daniel Westermann (DWE) ; >pgsql-general@lists.postgresql.org >Subject: Re: Temporary tablespaces on a RAM disk >On Tue, 2020-03-17 at 09:49 +, Daniel Westermann (DWE) wrote: >> is someone using temporary tablespace

Re: Temporary tablespaces on a RAM disk

>"Daniel Westermann (DWE)" writes: >>> People have asked about this before, so maybe it'd be an idea to make >>> an explicit concept of a temp tablespace that only accepts temp tables, >>> and do whatever is needful to make that robust. But I've not heard of >>> any work towards that. >> That's

Re: Temporary tablespaces on a RAM disk

On Tue, 2020-03-17 at 09:49 +, Daniel Westermann (DWE) wrote: > is someone using temporary tablespaces on a RAM disk ? Any experiences with > that? > I did some quick tests and checked the archives but could not find any > information that either confirmed it is a bad idea nor the opposite.

Re: Temporary tablespaces on a RAM disk

"Daniel Westermann (DWE)" writes: >> People have asked about this before, so maybe it'd be an idea to make >> an explicit concept of a temp tablespace that only accepts temp tables, >> and do whatever is needful to make that robust. But I've not heard of >> any work towards that. > That's what I

Re: Temporary tablespaces on a RAM disk

Ron writes: > On 3/17/20 12:19 PM, David G. Johnston wrote: >> There is a nice big bold warning callout in the documentation that covers >> this explicitly. >> https://www.postgresql.org/docs/12/manage-ag-tablespaces.html >>> Warning >>> Placing a tablespace on a temporary file system like a RAM

Re: Temporary tablespaces on a RAM disk

Daniel Westermann wrote: > is someone using temporary tablespaces on a RAM disk ? Any experiences with > that? > I did some quick tests and checked the archives but could not find any > information that either confirmed it is a bad idea nor the opposite. >Well, we point our "stats_temp_dir

Re: Temporary tablespaces on a RAM disk

On Tue, Mar 17, 2020 at 2:49 AM Daniel Westermann (DWE) mailto:daniel.westerm...@dbi-services.com>> wrote: is someone using temporary tablespaces on a RAM disk ? Any experiences with that? I did some quick tests and checked the archives but could not find any information that either confirmed i

Re: Temporary tablespaces on a RAM disk

Ron writes: > On 3/17/20 12:19 PM, David G. Johnston wrote: >> There is a nice big bold warning callout in the documentation that covers >> this explicitly. >> https://www.postgresql.org/docs/12/manage-ag-tablespaces.html >>> Warning >>> Placing a tablespace on a temporary file system like a RAM

Re: Temporary tablespaces on a RAM disk

On 3/17/20 12:19 PM, David G. Johnston wrote: On Tue, Mar 17, 2020 at 2:49 AM Daniel Westermann (DWE) > wrote: is someone using temporary tablespaces on a RAM disk ? Any experiences with that? I did some quick tests and checked the archiv

Re: Temporary tablespaces on a RAM disk

On Tue, Mar 17, 2020 at 2:49 AM Daniel Westermann (DWE) < daniel.westerm...@dbi-services.com> wrote: > is someone using temporary tablespaces on a RAM disk ? Any experiences > with that? > I did some quick tests and checked the archives but could not find any > information that either confirmed it

RE: Temporary tablespaces on a RAM disk

Daniel Westermann wrote: > is someone using temporary tablespaces on a RAM disk ? Any experiences with > that? > I did some quick tests and checked the archives but could not find any > information that either confirmed it is a bad idea nor the opposite. Well, we point our "stats_temp_directory

Re: Order by and timestamp SOLVED

> > I am still not sure that this can be marked solved. I am trying to figure out > how running a different version of psql once can affect another version of > psql. That would seem to imply psql changed something on the server and AFAIK > sorting/ordering is done by the server not the clien

Re: Keeping Admin-Owner user but creating new user with effective Admin-Owner access rights?

On 3/17/20 8:23 AM, AC Gomez wrote: We have the following scenario... We've inherited a situation where we have a master admin user that's used across the board for all processes. We need to undo that one process at a time. So, for each process we thought of creating two secondary users, am

Re: Order by and timestamp SOLVED

On 3/17/20 12:28 AM, Björn Lundin wrote: So insert is bnl@ibm2:~/db$ psql Tidtagning är på. AUTOCOMMIT off psql (9.6.15, server 9.4.15) Skriv "help" för hjälp. Except you are using psql 9.6.15 against a 9.4.15 server. What happens if you use psql(9.4.15) to do sort query against 9.4.15 se

Keeping Admin-Owner user but creating new user with effective Admin-Owner access rights?

We have the following scenario... We've inherited a situation where we have a master admin user that's used across the board for all processes. We need to undo that one process at a time. So, for each process we thought of creating two secondary users, among which we will rotate a password. Howe

Re: Mixed Locales and Upgrading

Don Seiler writes: > On Tue, Mar 17, 2020 at 8:56 AM Tom Lane wrote: >> Yikes. Well, if there aren't obvious operational problems, it might be >> that the data is actually UTF8-clean, or almost entirely so. Maybe you >> could look at the problem as being one of validation. > For this test, wou

Re: Order by and timestamp SOLVED

17 mars 2020 kl. 15:05 skrev Tom Lane : > > =?utf-8?Q?Bj=C3=B6rn_Lundin?= writes: >>> What happens if you use psql(9.4.15) to do sort query against 9.4.15 server? > >> However AFTER I checked the bad sorting - sep/oct 2016 and early apr 2017 >> With psql 9.4 I connected with psql 9.6 again. >>

Re: Mixed Locales and Upgrading

On Tue, Mar 17, 2020 at 8:56 AM Tom Lane wrote: > > Yikes. Well, if there aren't obvious operational problems, it might be > that the data is actually UTF8-clean, or almost entirely so. Maybe you > could look at the problem as being one of validation. In that case, > it'd be possible to consid

Re: Order by and timestamp SOLVED

=?utf-8?Q?Bj=C3=B6rn_Lundin?= writes: >> What happens if you use psql(9.4.15) to do sort query against 9.4.15 server? > However AFTER I checked the bad sorting - sep/oct 2016 and early apr 2017 > With psql 9.4 I connected with psql 9.6 again. > And now the sorting error is gone her too! Boy ..

Re: Mixed Locales and Upgrading

Don Seiler writes: > On Mon, Mar 16, 2020 at 10:28 AM Tom Lane wrote: >> I don't think you should use pg_upgrade here at all. A dump/restore >> is really the only way to make sure that you have validly encoded data. > That is what I thought, and probably not what they'll want to hear given > th

Re: Mixed Locales and Upgrading

On Tue, Mar 17, 2020 at 8:06 AM Don Seiler wrote: > On Mon, Mar 16, 2020 at 10:28 AM Tom Lane wrote: > >> > Well, in principle you could likewise manually update pg_database's >> datcollate and datctype columns to say "en_US.utf8". However, there's >> a much bigger problem here --- what steps i

Re: Mixed Locales and Upgrading

On Mon, Mar 16, 2020 at 10:28 AM Tom Lane wrote: > > Egad. > My thoughts exactly. > Well, in principle you could likewise manually update pg_database's > datcollate and datctype columns to say "en_US.utf8". However, there's > a much bigger problem here --- what steps if any did this cowboy ta

Re: Automatic failover

Hi Sonam, On Tue, Mar 17, 2020 at 11:30 AM Sonam Sharma wrote: > > I have setup replication using repmgr. Wanted to know how much time the slave > node will take to become new primary ?? If any document, can someone please > share of auto failover. With automatic failover, how much time the sl

Automatic failover

I have setup replication using repmgr. Wanted to know how much time the slave node will take to become new primary ?? If any document, can someone please share of auto failover. With automatic failover, how much time the slave takes to become new primary . Thanks..

Temporary tablespaces on a RAM disk

Hi, is someone using temporary tablespaces on a RAM disk ? Any experiences with that? I did some quick tests and checked the archives but could not find any information that either confirmed it is a bad idea nor the opposite. Thanks in advance Daniel

Re: Order by and timestamp SOLVED

>> So insert is >> bnl@ibm2:~/db$ psql >> Tidtagning är på. >> AUTOCOMMIT off >> psql (9.6.15, server 9.4.15) >> Skriv "help" för hjälp. > > Except you are using psql 9.6.15 against a 9.4.15 server. > > What happens if you use psql(9.4.15) to do sort query against 9.4.15 server? So this is mo