Re: Can't seem to mix an inner and outer join in a query and get it to work right.

[David G. Johnston]

On Monday, June 29, 2020, David Gauthier  wrote:

>
>sqf_id   | sqf_sl  |  as_cl  |
> wa_id |   type
>
> +---
> --+-+---+---
>
> * arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
> |  2772 | autosmoke*
>
>  arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
> |  2773 |
>
>  arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363
> |  2774 |
>
> * bgregory_20.06.29-09:46:49_raphael_main@1277530| 1277949 |
> |   |*
> (4 rows)
>
>
> dvm.workarea_env on
> dvdb-#   (sqf.sqf_runs.submitted_
> changelist=dvm.workarea_env.p4_changelist)
> dvdb-#*inner join* dvm.dvm_events on
> dvdb-#   (dvm.workarea_env.wa_id = dvm.dvm_events.wa_id
> and dvm.dvm_events.type = 'autosmoke')
> dvdb-# where sqf.sqf_Runs.submitted_shelvelist in (4404957,1277949);
>sqf_id   | sqf_sl  |  as_cl  |
> wa_id |   type
> +---
> --+-+---+---
>  arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363 |
>  2772 | autosmoke
> (1 row)
>
>
> Is there a way to retain the effect of that first outer join while
> enforcing that inner join ?
>

The quick non-testable answer is most likely.  I usually end up doing
trial-and-error and adding possibly unnecessary parentheses to force the
needed order of operations (or moving part of the join into an explicit
subquery, possibly using a CTE/WITH).  Right now it seems that the query is
forcing, via the inner join, a final output where the column
type=‘autosmoke’.  i.e., its doing the outer join first then the inner.
You seem to want the reverse.

David J.

Re: libpq pipelineing

[Samuel Williams]

Tom, I'm implementing a small abstraction layer for event-driven
result streaming on PostgreSQL and MariaDB for Ruby, and I'll endeavor
to report back with some numbers once I have enough of it working to
benchmark something meaningful.

Thanks for your patience and help.

Kind regards,
Samuel

On Tue, 30 Jun 2020 at 02:06, Tom Lane  wrote:
>
> Samuel Williams  writes:
> > Those methods don't seem to have an equivalent in libpq - you can use
> > PQgetResult but it buffers all the rows. Using single row mode results
> > in many results for each query (seems like a big overhead).
>
> Have you got any actual evidence for that?  Sure, the overhead is
> more than zero, but does it mean anything in comparison to the other
> costs of data transmission?
>
> > Maybe the statement about efficiency is incorrect, but it would be
> > nice if you could incrementally stream a single result set more
> > easily.
>
> More easily than what?  If we did not construct a PGresult then we would
> need some other abstraction for access to the returned row, dealing with
> error cases, etc etc.  That would mean a lot of very duplicative API code
> in libpq, and a painful bunch of adjustments in client code.
>
> regards, tom lane

Can't seem to mix an inner and outer join in a query and get it to work right.

[David Gauthier]

9.6.7 on linux

This query, which has 2 outer joins, gives me the records that I want...

dvdb=# select

dvdb-#   sqf.sqf_runs.sqf_id,

dvdb-#   sqf.sqf_runs.submitted_shelvelist as sqf_sl,

dvdb-#   dvm.workarea_env.p4_changelist as as_cl,

dvdb-#   dvm.workarea_env.wa_id,

dvdb-#   dvm.dvm_events.type

dvdb-# from

dvdb-#   sqf.sqf_runs left outer join dvm.workarea_env on

dvdb-#
(sqf.sqf_runs.submitted_changelist=dvm.workarea_env.p4_changelist)

dvdb-#left outer join dvm.dvm_events on

dvdb-#   (dvm.workarea_env.wa_id = dvm.dvm_events.wa_id and
dvm.dvm_events.type = 'autosmoke')

dvdb-# where sqf.sqf_Runs.submitted_shelvelist in (4404957,1277949);

   sqf_id   | sqf_sl  |  as_cl  |
wa_id |   type

+-+-+---+---

* arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363 |
2772 | autosmoke*

 arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363 |
2773 |

 arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363 |
2774 |

* bgregory_20.06.29-09:46:49_raphael_main@1277530| 1277949 |
|   |*
(4 rows)


The last 3 rows have nothing in the last column because of the first outer
join in the query.
The last row has nothing in the 3rd and 4th columns because of the 2nd
outer join.

I want to change the query to keep the first and last rows.  So I was
thinking to change the second outer join to an inner join.  But when I run
it, I lose the 4th record...

dvdb=# select
dvdb-#   sqf.sqf_runs.sqf_id,
dvdb-#   sqf.sqf_runs.submitted_shelvelist as sqf_sl,
dvdb-#   dvm.workarea_env.p4_changelist as as_cl,
dvdb-#   dvm.workarea_env.wa_id,
dvdb-#   dvm.dvm_events.type
dvdb-# from
dvdb-#   sqf.sqf_runs left outer join dvm.workarea_env on
dvdb-#
(sqf.sqf_runs.submitted_changelist=dvm.workarea_env.p4_changelist)
dvdb-#*inner join* dvm.dvm_events on
dvdb-#   (dvm.workarea_env.wa_id = dvm.dvm_events.wa_id and
dvm.dvm_events.type = 'autosmoke')
dvdb-# where sqf.sqf_Runs.submitted_shelvelist in (4404957,1277949);
   sqf_id   | sqf_sl  |  as_cl  |
wa_id |   type
+-+-+---+---
 arazhang_20.06.28-20:59:06_soc_navi24_main@4404799 | 4404957 | 4405363 |
 2772 | autosmoke
(1 row)


Is there a way to retain the effect of that first outer join while
enforcing that inner join ?

Re: PostgreSQL database segsize

[Tom Lane]

Magnus Hagander  writes:
> On Tue, Jun 30, 2020 at 12:17 AM Bill Glennon  wrote:
>> If you are building a Postgresql database from source and you use
>> option --with-segsize=4, how do you verify that the database segsize is 4GB
>> and not the default 1GB? Is there a query that you can run?

> You can run the query "SHOW segment_size" to show the compiled-in value.

pg_controldata will show it too, though a bit more opaquely:

Blocks per segment of large relation: 131072

This would be helpful if you have an on-disk database and no running
server.

regards, tom lane

Re: PostgreSQL database segsize

[Bill Glennon]

Awesome! That worked. Thank you Magnus!
I should have thought of that earlier. LOL

On Mon, Jun 29, 2020 at 6:33 PM Magnus Hagander  wrote:

>
>
> On Tue, Jun 30, 2020 at 12:17 AM Bill Glennon  wrote:
>
>> Hi,
>>
>> If you are building a Postgresql database from source and you use
>> option --with-segsize=4, how do you verify that the database segsize is 4GB
>> and not the default 1GB? Is there a query that you can run?
>>
>> Or even if you come into a place to support an existing PostgreSQL
>> database, how do you find out what the database segsize is?
>>
>>
> You can run the query "SHOW segment_size" to show the compiled-in value.
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/ 
>  Work: https://www.redpill-linpro.com/ 
>

Re: PostgreSQL database segsize

[Magnus Hagander]

On Tue, Jun 30, 2020 at 12:17 AM Bill Glennon  wrote:

> Hi,
>
> If you are building a Postgresql database from source and you use
> option --with-segsize=4, how do you verify that the database segsize is 4GB
> and not the default 1GB? Is there a query that you can run?
>
> Or even if you come into a place to support an existing PostgreSQL
> database, how do you find out what the database segsize is?
>
>
You can run the query "SHOW segment_size" to show the compiled-in value.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

PostgreSQL database segsize

[Bill Glennon]

Hi,

If you are building a Postgresql database from source and you use
option --with-segsize=4, how do you verify that the database segsize is 4GB
and not the default 1GB? Is there a query that you can run?

Or even if you come into a place to support an existing PostgreSQL
database, how do you find out what the database segsize is?

Thanks in advance for your help.

-- Bill

Re: EXTERNAL: Re: Netapp SnapCenter

[Paul Förster]

Hi Stephen,

> On 29. Jun, 2020, at 17:29, Stephen Frost  wrote:
> 
> I typically recommend rather strongly against using anything that won't
> allow you to update to keep pace with PG minor versions, considering
> they include critical security and corruption fixes (in some cases, but
> even if they don't, they're important to install).

I know and I totally agree.

> Time to consider an alternative product, imv.

again, I absolutely agree. I told them so but I can't influence that. So I'm 
basically stuck with the versions we have for all databases which are involved 
in Qlik replications. All others I can upgrade at will, and I do so. :-)

Cheers,
Paul

Re: pgbench and timestamps

[Fabien COELHO]

Hello Tom,

The attached patch fixes some of the underlying problems reported by 
delaying the :var to $1 substitution to the last possible moments, so that 
what variables are actually defined is known. PREPARE-ing is also delayed 
to after these substitutions are done.


It requires a mutex around the commands, I tried to do some windows 
implementation which may or may not work.


The attached patch fixes (2) & (3) for extended & prepared.

I have a doubt about fixing (1) because it would be a significant 
behavioral change and it requires changing the replace routine 
significantly to check for quoting, comments, and so on. This means that 
currently ':var' is still broken under -M extended & prepared, I could 
only break it differently by providing a nicer error message and also 
break it under simple whereas it currently works there. I'm not thrilled 
by spending efforts to do that.


The patches change the name of "parseQuery" to "makeVariablesParameters", 
because it was not actually parsing any query. Maybe the new name could be 
improved.


In passing, there was a bug in how NULL was passed, which I tried to fix
as well.


I don't often do much with pgbench and variables, but there are a few
things that surprise me here.
1) That pgbench replaces variables within single quotes, and;
2) that we still think it's a variable name when it starts with a digit, and;
3) We replace variables that are undefined.



Also (4) this only happens when in non-simple query mode --- the
example works fine without "-M prepared".


After looking around in the code, it seems like the core of the issue
is that pgbench.c's parseQuery() doesn't check whether a possible
variable name is actually defined, unlike assignVariables() which is
what does the same job in simple query mode.  So that explains the
behavioral difference.


Yes.


The reason for doing that probably was that parseQuery() is run when
the input file is read, so that relevant variables might not be set
yet.  We could fix that by postponing the work to be done at first
execution of the query, as is already the case for PQprepare'ing the
query.


Yep, done at first execution of the Command, so that variables are known.


Also, after further thought I realize that (1) absolutely is a bug
in the non-simple query modes, whatever you think about it in simple
mode.  The non-simple modes are trying to pass the variable values
as extended-query-protocol parameters, and the backend is not going
to recognize $n inside a literal as being a parameter.


Yep. See my comments above.


If we fixed (1) and (3) I think there wouldn't be any great need
to tighten up (2).


I did (2) but not (1), for now.

--
Fabien.diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 8e728dc094..7436210fd4 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -998,15 +998,14 @@ pgbench  options  d
   
There is a simple variable-substitution facility for script files.
Variable names must consist of letters (including non-Latin letters),
-   digits, and underscores.
+   digits (but not on the first character), and underscores.
Variables can be set by the command-line -D option,
explained above, or by the meta commands explained below.
In addition to any variables preset by -D command-line options,
there are a few variables that are preset automatically, listed in
. A value specified for these
variables using -D takes precedence over the automatic presets.
-   Once set, a variable's
-   value can be inserted into a SQL command by writing
+   Once set, a variable's value can be inserted into a SQL command by writing
:variablename.  When running more than
one client session, each session has its own set of variables.
pgbench supports up to 255 variable uses in one
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 08a5947a9e..09ccf05db5 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -119,12 +119,24 @@ typedef int pthread_attr_t;
 
 static int	pthread_create(pthread_t *thread, pthread_attr_t *attr, void *(*start_routine) (void *), void *arg);
 static int	pthread_join(pthread_t th, void **thread_return);
+
+typedef HANDLE pthread_mutex_t;
+static void pthread_mutex_init(pthread_mutex_t *pm, void *unused);
+static void pthread_mutex_lock(pthread_mutex_t *pm);
+static void pthread_mutex_unlock(pthread_mutex_t *pm);
+static void pthread_mutex_destroy(pthread_mutex_t *pm);
+
 #elif defined(ENABLE_THREAD_SAFETY)
 /* Use platform-dependent pthread capability */
 #include 
 #else
 /* No threads implementation, use none (-j 1) */
 #define pthread_t void *
+#define pthread_mutex_t void *
+#define pthread_mutex_init(m, p)
+#define pthread_mutex_lock(m)
+#define pthread_mutex_unlock(m)
+#define pthread_mutex_destroy(m)
 #endif
 
 
@@ -422,7 +434,7 @@ typedef struct
 	instr_time	txn_begin;		/* used for measuring schedule lag times */
 	instr_time	stmt_begin;		

Re: EXTERNAL: Re: Netapp SnapCenter

[Stephen Frost]

Greetings,

* Paul Förster (paul.foers...@gmail.com) wrote:
> > On 29. Jun, 2020, at 16:43, Stephen Frost  wrote:
> > I certainly don't blame you, particularly given all the changes
> > regarding how restore is done which went into v12- obviously anything
> > that hasn't been updated since before v12 was released isn't going to
> > work with those changes.
> 
> we have 11.6 and 12.3. 11.6 because we (unfortunately) have to use Qlik 
> (formerly Attunity). And no, that again wasn't my decision. Databases which 
> are involved in Qlik replication can't be updated (yet) according to our Qlik 
> specialist. I had a big argument with him when I updated even from 11.5 to 
> 11.6. This means I can't even update to 11.8.

I typically recommend rather strongly against using anything that won't
allow you to update to keep pace with PG minor versions, considering
they include critical security and corruption fixes (in some cases, but
even if they don't, they're important to install).

Time to consider an alternative product, imv.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: EXTERNAL: Re: Netapp SnapCenter

[Paul Förster]

Hi Stephen,

> On 29. Jun, 2020, at 16:43, Stephen Frost  wrote:
> 
> On some database platforms it basically means "stop writing", in order
> to allow a backup to be taken, but that's not what happens on PG and any
> documentation about using PG's start/stop definitely shouldn't be
> talking about quiescing the database.

ah, so it's more like freezing. Thanks.

> I certainly don't blame you, particularly given all the changes
> regarding how restore is done which went into v12- obviously anything
> that hasn't been updated since before v12 was released isn't going to
> work with those changes.

we have 11.6 and 12.3. 11.6 because we (unfortunately) have to use Qlik 
(formerly Attunity). And no, that again wasn't my decision. Databases which are 
involved in Qlik replication can't be updated (yet) according to our Qlik 
specialist. I had a big argument with him when I updated even from 11.5 to 
11.6. This means I can't even update to 11.8.

Cheers,
Paul

Re: Postgresql HA Cluster

[Brajendra Pratap Singh]

Hi Albe,

Here is my one more concern regarding patroni and repmgr tool versions
compatibility with centos8 and postgresql 10/11/12 versions, could u plz
provide ur valuable output.

Thanks
Brajendra

On Mon, 29 Jun, 2020, 1:03 PM Laurenz Albe, 
wrote:

> On Sun, 2020-06-28 at 09:10 +0530, Brajendra Pratap Singh wrote:
> > Is there any functionality present in postgresql for High Availability
> Cluster where we can setup
> > multiple nodes/instances in READ-WRITE mode for single database so that
> incase of one node/instance
> > failure it will automatically failover the traffic to 2nd node/instance
> (without
> > failure or in less time) ,this is just like oracle RAC concept .
> >
> > Here High Availability Cluster means there will be zero downtime incase
> of any one node/instance failure.
>
> There is no such functionality built into PostgreSQL.
>
> An architecture like Oracle RAC is not ideal for high availability, since
> the
> ASM/Tablespace/Segment "file system" is a single point of failure.
>
> You can use Solutions like Patroni or repmgr for high availability.
>
> That would not provode a multi-master solution, though.  There are some
> commercial solutions for that, but be warned that it would require
> non-trivial
> changes to your application.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: EXTERNAL: Re: Netapp SnapCenter

[Stephen Frost]

Greetings,

* Paul Förster (paul.foers...@gmail.com) wrote:
> > On 29. Jun, 2020, at 15:32, Stephen Frost  wrote:
> > 
> > Presumably they mean 'quiesce', except that that *isn't* what PG's
> 
> yes, sorry, "quiece" was a typo on my part. I never fully understood what 
> they mean with "quiesce" anyway. But then, I'm not the storage specialist in 
> out company anyway.

On some database platforms it basically means "stop writing", in order
to allow a backup to be taken, but that's not what happens on PG and any
documentation about using PG's start/stop definitely shouldn't be
talking about quiescing the database.

> > start/stop backup calls do, and assuming that's what happens is quite
> > wrong and could lead to issues.
> > 
> > The PG start/stop backup calls do things like wait for a checkpoint to
> > happen and track when that checkpoint was and return that info along
> > with whatever the stopping point of the backup is- so that you can make
> > sure that you have all of the WAL between those two points, and so you
> > can create the backup_label file that's needed to indicate on restore
> > that you're restoring from a backup and not just doing crash recovery.
> > 
> > If it isn't an atomic snapshot across everything then start/stop calls
> > have to be done as well as all that other fun stuff.
> 
> that's exactly why I want control over pg_start_backup() and 
> pg_stop_backup(). It may be in the form of pre- and post-scripts, but I want 
> control over it. I just can't seem to build trust in a plugin that saw the 
> last release two years ago and which I can't even find out if it would allow 
> PITRs, works with the new API and such things.

I certainly don't blame you, particularly given all the changes
regarding how restore is done which went into v12- obviously anything
that hasn't been updated since before v12 was released isn't going to
work with those changes.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: libpq pipelineing

[Tom Lane]

Samuel Williams  writes:
> Those methods don't seem to have an equivalent in libpq - you can use
> PQgetResult but it buffers all the rows. Using single row mode results
> in many results for each query (seems like a big overhead).

Have you got any actual evidence for that?  Sure, the overhead is
more than zero, but does it mean anything in comparison to the other
costs of data transmission?

> Maybe the statement about efficiency is incorrect, but it would be
> nice if you could incrementally stream a single result set more
> easily.

More easily than what?  If we did not construct a PGresult then we would
need some other abstraction for access to the returned row, dealing with
error cases, etc etc.  That would mean a lot of very duplicative API code
in libpq, and a painful bunch of adjustments in client code.

regards, tom lane

Re: EXTERNAL: Re: Netapp SnapCenter

[Paul Förster]

Hi Stephen,

> On 29. Jun, 2020, at 15:32, Stephen Frost  wrote:
> 
> Presumably they mean 'quiesce', except that that *isn't* what PG's

yes, sorry, "quiece" was a typo on my part. I never fully understood what they 
mean with "quiesce" anyway. But then, I'm not the storage specialist in out 
company anyway.

> start/stop backup calls do, and assuming that's what happens is quite
> wrong and could lead to issues.
> 
> The PG start/stop backup calls do things like wait for a checkpoint to
> happen and track when that checkpoint was and return that info along
> with whatever the stopping point of the backup is- so that you can make
> sure that you have all of the WAL between those two points, and so you
> can create the backup_label file that's needed to indicate on restore
> that you're restoring from a backup and not just doing crash recovery.
> 
> If it isn't an atomic snapshot across everything then start/stop calls
> have to be done as well as all that other fun stuff.

that's exactly why I want control over pg_start_backup() and pg_stop_backup(). 
It may be in the form of pre- and post-scripts, but I want control over it. I 
just can't seem to build trust in a plugin that saw the last release two years 
ago and which I can't even find out if it would allow PITRs, works with the new 
API and such things.

I may be wrong here, but my gut feeling about this is just not good for some 
reason.

Cheers,
Paul

Re: libpq pipelineing

[Stephen Frost]

Greetings,

* Samuel Williams (space.ship.travel...@gmail.com) wrote:
> Here is a short example:
> 
> https://gist.github.com/ioquatix/2f08f78699418f65971035785c80cf18
> 
> It makes 10 queries in one "PQsendQuery" and sets single row mode. But
> all the results come back at once as shown by the timestamps.

If you have 10 queries that you want to make in a given transaction and
you care about the latency then really the best option is to wrap that
all in a single pl/pgsql function on the server side and make one call.

> Next I'm planning to investigate streaming large recordsets to see if
> it works better/incrementally.

If you want to stream large data sets to/from PG, you should consider
using COPY.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: PG13 Trusted Extension usability issue

[Stephen Frost]

Greetings,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> "Brad Nicholson"  writes:
> > Tom Lane  wrote on 2020/06/26 02:47:25 PM:
> >> I'm confused about your point here.  postgresql_fdw has intentionally
> >> *not* been marked trusted.  That's partly because it doesn't seem like
> >> outside-the-database access is something we want to allow by default,
> >> but it's also the case that there are inside-the-database permissions
> >> issues.
> 
> > Ah - I misread the docs.  Specifically I read this:
> > "For many extensions this means superuser privileges are needed. However,
> > if the extension is marked trusted in its control file, then it can be
> > installed by any user who has CREATE privilege on the current database"
> > To mean that you could mark any extension as trusted in the control file to
> > allow non-superuser installation.
> 
> Well, it's just like anything else in an open-source database: you can
> change the code however you want, but the fallout from that is on you.
> 
> In the case at hand, you might be able to do what you want by adding
> something along the line of
> 
> GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw
>   TO @extowner@ WITH GRANT OPTION;
> 
> to the extension install script.  But nobody's researched whether that's
> reasonable from a security standpoint, or whether it will play nicely
> with dump/reload, etc etc.

Indeed that could be done, and almost certainly will be by cloud
vendors, resulting in more forks of PG done in different ways.

I do agree that, in general, we need a way to allow a superuser to GRANT
the right to connect to other systems (so that it could be done
initially when creating the not-really-superuser-privileged-user, before
the extension is created), since we don't want that to be allowed from
the start, then have that checked by extensions, but once that's done it
seems like it'd be alright to include the above GRANT USAGE in upstream
(regarding this specific concern, at least).

Seems like a default role would probably be the way to add that.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: EXTERNAL: Re: Netapp SnapCenter

[Stephen Frost]

Greetings,

* Paul Förster (paul.foers...@gmail.com) wrote:
> > On 26. Jun, 2020, at 12:29, Magnus Hagander  wrote:
> > I believe NetApp does atomic snapshots across multiple volumes, if you have 
> > them in the same consistency group. (If you don't then you're definitely in 
> > for a world of pain if you ever have to restore)
> > 
> > Snapshotting multiple volumes in a consistency group will set up a write 
> > fence across them, then snapshot, and AIUI guarantees correct write 
> > ordering.
> 
> That's how I understood it too. But it will be difficult in our complex 
> storage world to get the storage guys to create a consistency group.
> 
> Also, what I'm looking for is a plugin that allows to, how Netapp call it, 
> "quiece" the PostgreSQL cluster, i.e. does start/stop backup.

Presumably they mean 'quiesce', except that that *isn't* what PG's
start/stop backup calls do, and assuming that's what happens is quite
wrong and could lead to issues.

The PG start/stop backup calls do things like wait for a checkpoint to
happen and track when that checkpoint was and return that info along
with whatever the stopping point of the backup is- so that you can make
sure that you have all of the WAL between those two points, and so you
can create the backup_label file that's needed to indicate on restore
that you're restoring from a backup and not just doing crash recovery.

If it isn't an atomic snapshot across everything then start/stop calls
have to be done as well as all that other fun stuff.

> I don't really trust the community plugin as it is so old and I'm not sure it 
> gets any more development attention. For example, what about the API change 
> (exclusive vs. non-exclusive)? Does it use the new API?

No clue.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Help - Need 9.3 for Centos - recovery of legacy system

[Adrian Klaver]

On 6/29/20 6:02 AM, Adrian Klaver wrote:

On 6/29/20 5:04 AM, Brad Thompson wrote:
Need to get 9.3 to install to recover a legacy system.  We have a 
legacy system that experienced a hardware failure requiring a OS 
reload.  Have installed 9.6 as an interim step but it won't let me get 
to the 9.3 databases.


So you have the 9.3 data directories, but not the 9.3 binaries to use 
the them?




Am hoping to use the migration tools to get the 9.3 data dumped then 
reloaded into newer version.


Can anyone assist?


What OS and it's version?


It would help if I read the subject. Still not sure of the version, but 
there are EOL versions available here:


https://yum.postgresql.org/repopackages.php


Or you could build 9.3 from source:

https://www.postgresql.org/ftp/source/v9.3.25/

and then dump from the 9.3 running instance using the 9.6 pg_dump.



Thanks in advance






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Help - Need 9.3 for Centos - recovery of legacy system

[Adrian Klaver]

On 6/29/20 5:04 AM, Brad Thompson wrote:
Need to get 9.3 to install to recover a legacy system.  We have a legacy 
system that experienced a hardware failure requiring a OS reload.  Have 
installed 9.6 as an interim step but it won't let me get to the 9.3 
databases.


So you have the 9.3 data directories, but not the 9.3 binaries to use 
the them?




Am hoping to use the migration tools to get the 9.3 data dumped then 
reloaded into newer version.


Can anyone assist?


What OS and it's version?

Or you could build 9.3 from source:

https://www.postgresql.org/ftp/source/v9.3.25/

and then dump from the 9.3 running instance using the 9.6 pg_dump.



Thanks in advance



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: libpq pipelineing

[Samuel Williams]

I think libmariadb has a nicer interface for this.

Essentially what you do is send your query, and then read a result set
(one result set per query), and then you stream individual rows using:

mysql_fetch_row_start
mysql_fetch_row_cont

Those methods don't seem to have an equivalent in libpq - you can use
PQgetResult but it buffers all the rows. Using single row mode results
in many results for each query (seems like a big overhead). The
difference between this and MariaDB is that mysql_fetch_row still
operates within one logical set of results, but single row mode breaks
the single logical set of results into lots of individual results.

Maybe the statement about efficiency is incorrect, but it would be
nice if you could incrementally stream a single result set more
easily.


On Sun, 28 Jun 2020 at 02:40, Tom Lane  wrote:
>
> Samuel Williams  writes:
> > Here is a short example:
> > https://gist.github.com/ioquatix/2f08f78699418f65971035785c80cf18
> > It makes 10 queries in one "PQsendQuery" and sets single row mode. But
> > all the results come back at once as shown by the timestamps.
>
> That looks to be less about what libpq will do than what the Ruby
> interface code will do.
>
> The volume of return data may also be an issue.  I don't think the
> backend will flush data out to the client except when it (a) reaches
> an idle state or (b) fills the output buffer.  Ten occurrences of
> a short query result aren't gonna be enough for (b) --- from memory,
> that buffer is probably 8KB.
>
> regards, tom lane

Help - Need 9.3 for Centos - recovery of legacy system

[Brad Thompson]

Need to get 9.3 to install to recover a legacy system.  We have a legacy
system that experienced a hardware failure requiring a OS reload.  Have
installed 9.6 as an interim step but it won't let me get to the 9.3
databases.

Am hoping to use the migration tools to get the 9.3 data dumped then
reloaded into newer version.

Can anyone assist?

Thanks in advance

Re: Postgresql HA Cluster

[Andreas Joseph Krogh]

På mandag 29. juni 2020 kl. 09:40:13, skrev Andreas Kretschmer <
andr...@a-kretschmer.de >: 


 Am 29.06.20 um 09:33 schrieb Laurenz Albe:
 > That would not provode a multi-master solution, though. There are some
 > commercial solutions for that, but be warned that it would require 
non-trivial
 > changes to your application.

 not really with BDR3 ;-) 

Well, BDR, last time I checked, still doesn't support exclusion-constraints, 
so it's not a drop-in replacement. 


--
 Andreas Joseph Krogh 

Re: Postgresql HA Cluster

[Andreas Kretschmer]

Am 29.06.20 um 09:33 schrieb Laurenz Albe:

That would not provode a multi-master solution, though.  There are some
commercial solutions for that, but be warned that it would require non-trivial
changes to your application.


not really with BDR3 ;-)


Andreas


--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Postgresql HA Cluster

[Laurenz Albe]

On Sun, 2020-06-28 at 09:10 +0530, Brajendra Pratap Singh wrote:
> Is there any functionality present in postgresql for High Availability 
> Cluster where we can setup
> multiple nodes/instances in READ-WRITE mode for single database so that 
> incase of one node/instance
> failure it will automatically failover the traffic to 2nd node/instance 
> (without
> failure or in less time) ,this is just like oracle RAC concept .
> 
> Here High Availability Cluster means there will be zero downtime incase of 
> any one node/instance failure.

There is no such functionality built into PostgreSQL.

An architecture like Oracle RAC is not ideal for high availability, since the
ASM/Tablespace/Segment "file system" is a single point of failure.

You can use Solutions like Patroni or repmgr for high availability.

That would not provode a multi-master solution, though.  There are some
commercial solutions for that, but be warned that it would require non-trivial
changes to your application.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com