Apologies for the rather naive question.
I have a psql migration script to call a looping procedure which commits
batches of data along the following lines:
set search_path = a, b c;
\set AUTOCOMMIT on
-- call procedure with nested transactions
CALL c.pr_my_procedure(debug=>true);
Based on my initial testing native logical replication seems easier and faster
to setup than pglogical, and may be easier for our team to administer.
One sticking point is monitoring the health of native logical replication.
Are there some native logical replication functions or calls that can, f
On 23/07/22, Ludwig Isaac Lim (ludz_...@yahoo.com) wrote:
> Below is a sample case that exhibits a behavior that I can't explain:
>
> -- create the table
> create table ts (t timestamp without time zone);
>
> -- populate
> insert into ts(t) values ('2022-07-16 00:22:06.974000');
> insert into ts(
I'm keen to learn of the differences between logical replication in
PostgreSQL 14 and how this is different from the pglogical extension.
Our intended use case is to have 200-300 publishers off many small
databases aggregating to 20-30 central databases on another PostgreSQL
instance on the same m
On 29/07/21, Matthias Apitz (g...@unixarea.de) wrote:
> El día miércoles, julio 28, 2021 a las 07:30:24p. m. +0200, Matthias Apitz
> escribió:
> Resulted, that the size can be 20.12cm X 7.5cm. Attached is an updated
> version as PDF. Hints/bugs are welcome.
I personally find that, after \l (list
On 14/05/21, Durumdara (durumd...@gmail.com) wrote:
> ...
> midqry1 as (
> select coalesce(XDate , '0001-01-01'), * from prevqry
> ),
> midqry2 as (
> select ArtID, max(XDate) as Max_XDate from midqry1
> where acq = True
> group by ArtID
> )
> ...
>
> Result: ERROR: column reference "XDate
On 22/01/21, Benedict Holland (benedict.m.holl...@gmail.com) wrote:
> Sometimes it is easier to simply > replicate the existing bad process
> that a team agrees to rather than making > a better process.
As Alvar Aalto said in a lecture at MIT
It is not by temporary building that Parthenon com
On 22/01/21, Benedict Holland (benedict.m.holl...@gmail.com) wrote:
> I mean... the best? You just laid out a bunch of steps to define a process.
>
> > On Thu, Jan 21, 2021 at 11:12 PM sivapostg...@yahoo.com <
> > sivapostg...@yahoo.com> wrote:
> >
> >> So the solution to the issue will be
> >>
>
On 21/05/20, Michael Stephenson (domehead...@gmail.com) wrote:
> On Thu, May 21, 2020 at 10:36 AM Rory Campbell-Lange wrote
> > Presently I've been thinking of using triggers or materialized views in
> > each database to materialise data into a "matview" schema
On 21/05/20, Karsten Hilbert (karsten.hilb...@gmx.net) wrote:
> On Thu, May 21, 2020 at 03:35:59PM +0100, Rory Campbell-Lange wrote:
>
> > We have quite a few databases of type a and many of type b in a cluster.
> > Both a and b types are fairly complex and are differ
We have quite a few databases of type a and many of type b in a cluster.
Both a and b types are fairly complex and are different solutions to a
similar problem domain. All the databases are very read-centric, and all
database interaction is currently through plpgsql with no materialised
data.
Some
On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote:
> Thanks Rory, the machine has the capacity to pull through pg_dumps but like
> u rightly mentioned incremental backups mean that we will need to work with
> the wal's.. 18TB is what is the scary part and with compression I dont see
> it be
On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote:
> Hello All,
> I have very recently inherited an 18 TB DB that is running version 9.2.
> Apparently this database has never been backed up and I have been tasked to
> set in a periodic backup routine (weekly full & daily incremental) and du
On 09/05/20, Tim Cross (theophil...@gmail.com) wrote:
> David G. Johnston writes:
> > On Fri, May 8, 2020 at 9:26 AM David Gauthier
> > wrote:
> >> The idea is to send a report to the list when all the data has been
> >> collected for a particular job and the final status of the job is updated
>
On 18/04/20, Olivier Leprêtre (o.lepre...@gmail.com) wrote:
> I would like to generate a serie of serie and didnt find how. With
> generate_series. I can generate a serie
...
> But how can I repeat this serie several times ?
Something like this?
gotest=> select a, b from generate_series(1, 3) a
On 27/03/20, Andrew Gierth (and...@tao11.riddles.org.uk) wrote:
> >>>>> "Rory" == Rory Campbell-Lange writes:
>
> Rory> Or:
>
> Rory> iconv -f WINDOWS-1252 -t UTF-8 -c < tempfile2 > tempfile3
>
> No. That's just a convers
On 27/03/20, Andrew Gierth (and...@tao11.riddles.org.uk) wrote:
> > "Thomas" == Thomas Munro writes:
>
> Thomas> Something like this approach might be useful for fixing the CSV file:
>
> Thomas>
> https://codereview.stackexchange.com/questions/185821/convert-a-mix-of-latin-1-and-utf-8-to-
On 04/03/20, Rory Campbell-Lange (r...@campbell-lange.net) wrote:
> We have many databases of the same type separated for data governance
> reasons. They, however, share the same web front-end code.
>
> Presently, replacing functions and performing data updates on the
> databases
On 04/03/20, David G. Johnston (david.g.johns...@gmail.com) wrote:
> On Wed, Mar 4, 2020 at 4:41 PM Rory Campbell-Lange
> wrote:
>
> > Any idea on how to run execute_dynamic across many databases at roughly
> > the same time?
> >
> > I'm just wonderin
On 04/03/20, David G. Johnston (david.g.johns...@gmail.com) wrote:
> On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston
> wrote:
>
> > On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange <
> > r...@campbell-lange.net> wrote:
> >
> >> Any thoughts on
On 04/03/20, Guyren Howe (guy...@gmail.com) wrote:
> On Mar 4, 2020, at 14:33 , Rory Campbell-Lange
> wrote:
> >
> > Essentially we wish to reduce the window where the frontend and backend
> > aren't synchronised.
> >
> > If we have (for example) 2
On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> On 3/4/20 2:22 PM, Rory Campbell-Lange wrote:
> > On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> > > On 3/4/20 2:04 PM, Rory Campbell-Lange wrote:
> > > > We have many databases of th
On 04/03/20, Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> On 3/4/20 2:04 PM, Rory Campbell-Lange wrote:
> > We have many databases of the same type separated for data governance
> > reasons. They, however, share the same web front-end code.
> >
> > Present
We have many databases of the same type separated for data governance
reasons. They, however, share the same web front-end code.
Presently, replacing functions and performing data updates on the
databases in series often executes across all databases in less than a
minute. (The updates are current
On 19/07/19, Peter J. Holzer (hjp-pg...@hjp.at) wrote:
> On 2019-07-17 12:34:41 +0100, Rory Campbell-Lange wrote:
> > We make extensive use of postgresql 'contacting' an external process,
> > but our use case involves many databases in a cluster rather than many
> >
On 17/07/19, Luca Ferrari (fluca1...@gmail.com) wrote:
> On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika wrote:
> > That is basically still my main question. How do I start a
> > background job (e.g. a function) which waits by polling or LISTEN /
> > NOTIFY for records in a table to be processed.
>
> Y
On 01/04/19, Moreno Andreo (moreno.and...@evolu-s.it) wrote:
...
> I'm not forced to use pseudonimysation if there's the risk to get
> things worse in a system. I've got to speak about these"two opposing
> forces at work" to a privacy expert (maybe choosing another one, as
> Peter suggested :-) ) a
On 28/03/19, Perumal Raj (peruci...@gmail.com) wrote:
> On Sat, Mar 23, 2019 at 3:12 PM Kenneth Marshall wrote:
> > On Sat, Mar 23, 2019 at 12:09:11PM +0000, Rory Campbell-Lange wrote:
> > > On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote:
> > > >
On 23/03/19, Hans Schou (hans.sc...@gmail.com) wrote:
> On Sun, Mar 17, 2019 at 11:54 PM Rory Campbell-Lange <
> r...@campbell-lange.net> wrote:
>
> > We aren't sure whether to use software MDRaid or a MegaRAID card.
>
> Never go with hardRaid. I have had a bre
On 23/03/19, Andy Colson (a...@squeakycode.net) wrote:
> On 3/23/19 7:09 AM, Rory Campbell-Lange wrote:
> > On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote:
...
> > > We're buying some new Postgres servers with
> > >
> > >
On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote:
> We aren't sure whether to use software MDRaid or a MegaRAID card.
>
> We're buying some new Postgres servers with
>
> 2 x 240GB Intel SSD S4610 (RAID1 : system)
> 4 x 960GB Intel SSD S4610
We aren't sure whether to use software MDRaid or a MegaRAID card.
We're buying some new Postgres servers with
2 x 240GB Intel SSD S4610 (RAID1 : system)
4 x 960GB Intel SSD S4610 (RAID10 : db)
We'll be using Postgres 11 on Debian.
The MegaRAID 9271-8i with flash cache protection is ava
On 05/03/19, Kenneth Marshall (k...@rice.edu) wrote:
> >
> > Consequently we're thinking of the following replacement servers:
> >
> > postgres 11 (planned)
> > supermicro 113TQ-R700W
> > LSI MegaRAID 9271-8i SAS/SATA RAID Controller, 1Gb DDR3 Cache (PCIE-
> > Gen 3)
> > 500
We've been happy running a database server and replica for some years
with the following details and specs:
postgres 9.5 (currently)
supermicro X9DRD-7LN4F
LSI Megaraid MR9261-8i with BBU
250gb raid 1 /
224gb raid 10 /db
126GB RAM (1066Mhz DDR3)
2 x Xeon E5-2609
With a table like this:
Table "public.x"
Column | Type | Modifiers
+---+---
j | jsonb |
and data like this:
j
--
{"a": 1, "people": [{"
On 18/09/18, Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> Rory Campbell-Lange wrote:
> > We are looking to upgrade our current database server infrastructure so
> > that it is suitable for the next 3 years or so.
> >
> > We envisage needing about 800GB of primary d
[I sent this to the performance list a couple of days ago and received
no replies. Apologies for the cross-post]
We are looking to upgrade our current database server infrastructure so
that it is suitable for the next 3 years or so.
Presently we have two physical servers with the same specs:
Thanks for your comprehensive response, Adrian.
On 08/04/18, Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> On 04/08/2018 03:40 AM, Rory Campbell-Lange wrote:
> > On 07/04/18, Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> > > > (I'm aware that the reasons beh
On 07/04/18, Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> > (I'm aware that the reasons behind the change made to the dump format
> > due to CVE-2018-1058 are set out here:
> > https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path)
> >
>
> > Additionally we som
Hi
Following an upgrade to 9.5.12, we cannot restore some of our databases
due to a schema qualification issue introduced in the new postgres
version of pg_dump.
Specifically, the problem line is the addition of :
SELECT pg_catalog.set_config('search_path', '', false);
to the header of the
On 01/12/17, support-tiger (supp...@tigernassau.com) wrote:
> To diversify risk, we would like to have a daily or weekly data backup
> stored in another location besides the VPS service we are using - pg_dump is
> great for the backup but transferring a growing db across the internet to a
> local m
On 30/11/17, Durumdara (durumd...@gmail.com) wrote:
> Somewhere the users made mistakes on prices (stock).
>
> I need to search for big differences between values.
> For example:
>
> 20
> 21
> 21,5
> 30
> 28
> ..
> 46392 <-
> 46392 <-
You could use window functions
https://www.postgresql.org/doc
On 28/11/17, Rob Sargent (robjsarg...@gmail.com) wrote:
>
> On 11/28/2017 10:50 AM, Ted Toth wrote:
> > On Tue, Nov 28, 2017 at 11:19 AM, Rob Sargent wrote:
> > > > On Nov 28, 2017, at 10:17 AM, Ted Toth wrote:
> > > >
> > > > I'm writing a migration utility to move data from non-rdbms data
> >
43 matches
Mail list logo
