Re: 20220221-Clarification regarding PostgeSQL DB backup

[Daevor The Devoted]

Hi Karthick

In that case, your are options are:

1. Continuous WAL Archiving

This is "like" using diff backups, but not quite the same.

OR

2. Use a 3rd party app, for example Barman  or
pgBackRest 

Good luck!

~~Na-iem Dollie

On Mon, Feb 21, 2022 at 8:25 AM Techsupport  wrote:

> *Hi ~~Na-iem Dollie,*
>
>
>
> Thanks  for your reply,
>
>
>
> In SQL Server there is an option to take Full backup and Differential
> Backup for a particular database.
>
>
>
> Like that, we need to take the differential Backup in PostgreSQL. This is
> my primary need.
>
>
>
> *Thanks,*
>
> *Karthick Ramu*
>
>
>
>
>
> *From:* Daevor The Devoted [mailto:doll...@gmail.com]
> *Sent:* Monday, February 21, 2022 11:05 AM
> *To:* Techsupport
> *Cc:* pgsql-generallists.postgresql.org
> *Subject:* Re: 20220221-Clarification regarding PostgeSQL DB backup
>
>
>
> Hi Karthick
>
>
>
> I'm a little rusty on PG (it's been about a decade since I last worked
> intensely with it), but I seem to remember that restoring with indexes can
> be very slow. The faster approach is to restore *without* indexes, and
> then create the indexes once the data restore is complete.
>
>
>
> My knowledge may be outdated, so best to check with others that have more
> recent knowledge. However, it's probably worth investigating in the
> meantime.
>
>
>
> Okay, I just had a quick look at the documentation, and it seems there is
> an option to run multiple concurrent jobs for the time-consuming parts of a
> restore (which includes index creation):
>
>
>
> -j *number-of-jobs*
>
>
>
> see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check
> the docs for whatever PG version you have)
>
>
>
> Good luck!
>
>
>
> ~~Na-iem Dollie
>
>
>
> On Mon, Feb 21, 2022 at 6:59 AM Techsupport 
> wrote:
>
> Hi Team,
>
>
>
> We have used PG_DUMP to take backup of particular database from the
> PostgreSQL Server. It takes too long to restore the databases , which has
>  Half Billion records (almost 8 Hour)
>
>
>
> My primary need is to make the Differential and Incremental backup on the
> Windows Server. When I search there is a tool Barman and PG_BackRest is
> available. But that will be supported only for Linux based servers only
>
>
>
> Please suggest me a tool to make differential backup
>
>
>
> Thanks,
>
> Karthick Ramu
>
>

RE: 20220221-Clarification regarding PostgeSQL DB backup

[Techsupport]

Hi ~~Na-iem Dollie,

 

Thanks  for your reply,

 

In SQL Server there is an option to take Full backup and Differential Backup 
for a particular database. 

 

Like that, we need to take the differential Backup in PostgreSQL. This is my 
primary need.

 

Thanks,

Karthick Ramu

 

 

From: Daevor The Devoted [mailto:doll...@gmail.com] 
Sent: Monday, February 21, 2022 11:05 AM
To: Techsupport
Cc: pgsql-generallists.postgresql.org
Subject: Re: 20220221-Clarification regarding PostgeSQL DB backup

 

Hi Karthick

 

I'm a little rusty on PG (it's been about a decade since I last worked 
intensely with it), but I seem to remember that restoring with indexes can be 
very slow. The faster approach is to restore without indexes, and then create 
the indexes once the data restore is complete.

 

My knowledge may be outdated, so best to check with others that have more 
recent knowledge. However, it's probably worth investigating in the meantime.

 

Okay, I just had a quick look at the documentation, and it seems there is an 
option to run multiple concurrent jobs for the time-consuming parts of a 
restore (which includes index creation):

 

-j number-of-jobs

 

see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check the 
docs for whatever PG version you have)

 

Good luck!

 

~~Na-iem Dollie

 

On Mon, Feb 21, 2022 at 6:59 AM Techsupport mailto:techsupp...@sardonyx.in> > wrote:

Hi Team, 

 

We have used PG_DUMP to take backup of particular database from the PostgreSQL 
Server. It takes too long to restore the databases , which has  Half Billion 
records (almost 8 Hour)

 

My primary need is to make the Differential and Incremental backup on the 
Windows Server. When I search there is a tool Barman and PG_BackRest is 
available. But that will be supported only for Linux based servers only

 

Please suggest me a tool to make differential backup

 

Thanks,

Karthick Ramu

Re: 20220221-Clarification regarding PostgeSQL DB backup

[Ron]

On 2/20/22 10:59 PM, Techsupport wrote:


Hi Team, **

We have used PG_DUMP to take backup of particular database from the 
PostgreSQL Server. It takes too long to restore the databases , which has 
 Half Billion records (almost 8 Hour)


My primary need is to make the Differential and Incremental backup on the 
Windows Server. When I search there is a tool Barman and PG_BackRest is 
available. But that will be supported only for Linux based servers only


Please suggest me a tool to make differential backup



Please show us the full pg_dump command, including all parameters.

--
Angular momentum makes the world go 'round.

Re: 20220221-Clarification regarding PostgeSQL DB backup

[Daevor The Devoted]

Hi Karthick

I'm a little rusty on PG (it's been about a decade since I last worked
intensely with it), but I seem to remember that restoring with indexes can
be very slow. The faster approach is to restore *without* indexes, and then
create the indexes once the data restore is complete.

My knowledge may be outdated, so best to check with others that have more
recent knowledge. However, it's probably worth investigating in the
meantime.

Okay, I just had a quick look at the documentation, and it seems there is
an option to run multiple concurrent jobs for the time-consuming parts of a
restore (which includes index creation):

-j *number-of-jobs*

see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check
the docs for whatever PG version you have)

Good luck!

~~Na-iem Dollie

On Mon, Feb 21, 2022 at 6:59 AM Techsupport  wrote:

> Hi Team,
>
>
>
> We have used PG_DUMP to take backup of particular database from the
> PostgreSQL Server. It takes too long to restore the databases , which has
>  Half Billion records (almost 8 Hour)
>
>
>
> My primary need is to make the Differential and Incremental backup on the
> Windows Server. When I search there is a tool Barman and PG_BackRest is
> available. But that will be supported only for Linux based servers only
>
>
>
> Please suggest me a tool to make differential backup
>
>
>
> Thanks,
>
> Karthick Ramu
>

20220221-Clarification regarding PostgeSQL DB backup

[Techsupport]

Hi Team, 

 

We have used PG_DUMP to take backup of particular database from the
PostgreSQL Server. It takes too long to restore the databases , which has
Half Billion records (almost 8 Hour)

 

My primary need is to make the Differential and Incremental backup on the
Windows Server. When I search there is a tool Barman and PG_BackRest is
available. But that will be supported only for Linux based servers only

 

Please suggest me a tool to make differential backup

 

Thanks,

Karthick Ramu

Re: Strange results when casting string to double

[Tom Lane]

Carsten Klein  writes:
> Prior to restarting, I've implemented my own Python based versions of both
> int fegetround(void);
> int fesetround(int rounding_mode integer);

Ah, good idea!

> With those, I was able to proof, that actually the "wrong" rounding mode
> FE_DOWNWARD (0x400)
> was in effect for every new process/connection with all the described 
> effects on casting from string or numeric to double precision:

Thanks for confirming that that was the source of the problem.

> We'll likely never know, why ever the postmaster got tainted with that 
> FE_DOWNWARD (0x400) rounding mode.

Indeed.  It's hard to see any other explanation than "random bit flip"
though.  The postmaster is designed to run only a very small amount of
code, almost none of which is user-controllable.  Even if there were
somewhere some code that intended to change the rounding mode, explaining
how the postmaster got to that without crashing is a tall order.

> Two official math functions to get and set the session's rounding mode 
> provided by PostgreSQL could be a good add-on for any of the next 
> versions of the database. Thinking about it again... maybe that's just 
> too dangerous :-p

No, we'd absolutely not ever provide a supported function to change
rounding modes.  That would require downgrading all float-related
functions from IMMUTABLE to STABLE, which would have severe performance
consequences, even for people with no interest in changing modes.

Thanks for closing out the thread with this info!

regards, tom lane

Re: Additional accessors via the Extension API ?

[Tom Lane]

Julien Rouhaud  writes:
> On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote:
>> Is this documented & expected behavior or it’s just happens to work?

> I don't think it's documented but it's an expected behavior, see
> https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_func.c#L57-L88

It is documented, near the bottom of this section:

https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE

Other relevant oddities are mentioned in

https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS

regards, tom lane

Re: Strange results when casting string to double

[Carsten Klein]

On 19.02.2022 20:34 Tom Lane wrote:


Per grep, there is no call of fesetround() in the Postgres source
tree.  I'm not sure offhand whether libc exposes any other APIs
that could change the rounding mode, but I am quite sure that we
wouldn't be intentionally changing it anywhere.

The OS would surely allow each process to have its own setting of the
rounding mode, so I doubt you can see it from outside.

Another point to keep in mind is that no matter how invasive that
import script might be, it's still hard to explain how it'd affect
the rounding mode in other backend processes.  You have to postulate
either that the rounding mode has been changed in the postmaster
process (and then inherited by session backends via fork()), or that
some code running at the time of child process creation changes the
mode, or that they replaced numeric_float8 with something else.

I think the only way that the postmaster's rounding mode could change
after postmaster start is the cosmic-ray hypothesis; while we do have
features that'd allow loading extra code into the postmaster, I'm
pretty sure they only take effect at postmaster start.  So even if
that import script tried to do that, it wouldn't have succeeded yet.

Of the other two hypotheses, "substitute numeric_float8" seems like
the most likely, especially given the other stuff you mentioned the
script doing.  Have you checked the relevant pg_cast entry to see
if it's been changed?  It'd also be interesting to see if the odd
rounding behavior happens in all databases of the cluster or just
one.


The script has finished!

After a restart of the database, everything works as expected again. 
Rounding as well as text/numeric to double precision works the same on 
all of my servers.


Prior to restarting, I've implemented my own Python based versions of both

int fegetround(void);

int fesetround(int rounding_mode integer);

Have a look a these:

CREATE OR REPLACE FUNCTION fegetround()
  RETURNS integer AS
$BODY$
if 'fn.fegetround' in SD:
return SD['fn.fegetround']()

from ctypes import cdll
from ctypes.util import find_library
libm = cdll.LoadLibrary(find_library('m'))
def fegetround():
return libm.fegetround()

SD['fn.fegetround'] = fegetround
return SD['fn.fegetround']()
 $BODY$
  LANGUAGE plpython3u VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION fesetround(rounding_mode integer)
  RETURNS integer AS
$BODY$
if 'fn.fesetround' in SD:
return SD['fn.fesetround'](rounding_mode)

from ctypes import cdll
from ctypes.util import find_library
libm = cdll.LoadLibrary(find_library('m'))
def fesetround(rounding_mode):
return libm.fesetround(rounding_mode)

SD['fn.fesetround'] = fesetround
return SD['fn.fesetround'](rounding_mode)
 $BODY$
  LANGUAGE plpython3u VOLATILE STRICT
  COST 100;

With those, I was able to proof, that actually the "wrong" rounding mode

FE_DOWNWARD (0x400)

was in effect for every new process/connection with all the described 
effects on casting from string or numeric to double precision:


SELECT 1.56::double precision
-> 1.55

Setting rounding mode to

FE_TONEAREST (0x0),

instantly lead back to the expected casting behavior:

SELECT 1.56::double precision
-> 1.56

Setting rounding mode after restarting the database is still possible, 
however, new sessions start off with the "correct" rounding mode 
FE_TONEAREST (0x0). So, the only thing that's really changed after the 
restart was, that the postmaster now has the "correct" rounding mode, 
which it promotes down when forking off child processes.


We'll likely never know, why ever the postmaster got tainted with that 
FE_DOWNWARD (0x400) rounding mode.


As Tom Lane said, no matter how aggressive the script could be, it can, 
if at all, only change its current session's rounding mode. So, maybe it 
actually was a random bit flip or a side effect caused by a quite rare 
error condition in postmaster.


Nearly the same is true for any core functions or casts hijacked by the 
script - these are only in effect for the database the script was ever 
connecting to. In my case, the script only used one database. However, 
the issue was present with any database.


Two official math functions to get and set the session's rounding mode 
provided by PostgreSQL could be a good add-on for any of the next 
versions of the database. Thinking about it again... maybe that's just 
too dangerous :-p


Finally, many thanks to all that supported me and came up with that many 
helpful ideas! :-)


Regards, Carsten

Re: Additional accessors via the Extension API ?

[Markur Sens]

> On 20 Feb 2022, at 12:35 PM, Julien Rouhaud  wrote:
> 
> On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote:
>>> 
>>> Maybe you could rely on some old grammar hack to have something a bit 
>>> similar,
>>> as (expr).funcname is an alias for funcname(expr).  For instance:
>> 
>> Is this documented & expected behavior or it’s just happens to work?
> 
> I don't think it's documented but it's an expected behavior, see
> 
> https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_func.c#L57-L88
> 

Ah thanks for this

> /*
> * Parse a function call
> *
> * For historical reasons, Postgres tries to treat the notations tab.col
> * and col(tab) as equivalent: if a single-argument function call has an
> * argument of complex type and the (unqualified) function name matches
> * any attribute of the type, we can interpret it as a column projection.

and the (unqualified) function name matches
*   any attribute of the type, we can interpret it as a column projection.


> * Conversely a function of a single complex-type argument can be written
> * like a column reference, allowing functions to act like computed 
> columns.
> *
> * If both interpretations are possible, we prefer the one matching the
> * syntactic form, but otherwise the form does not matter.
> *
> * Hence, both cases come through here.  If fn is null, we're dealing with
> * column syntax not function syntax.  In the function-syntax case,
> * the FuncCall struct is needed to carry various decoration that applies
> * to aggregate and window functions.
> [...]

Re: Additional accessors via the Extension API ?

[Julien Rouhaud]

On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote:
> >
> > Maybe you could rely on some old grammar hack to have something a bit 
> > similar,
> > as (expr).funcname is an alias for funcname(expr).  For instance:
> 
> Is this documented & expected behavior or it’s just happens to work?

I don't think it's documented but it's an expected behavior, see

https://github.com/postgres/postgres/blob/master/src/backend/parser/parse_func.c#L57-L88

/*
 *  Parse a function call
 *
 *  For historical reasons, Postgres tries to treat the notations tab.col
 *  and col(tab) as equivalent: if a single-argument function call has an
 *  argument of complex type and the (unqualified) function name matches
 *  any attribute of the type, we can interpret it as a column projection.
 *  Conversely a function of a single complex-type argument can be written
 *  like a column reference, allowing functions to act like computed 
columns.
 *
 *  If both interpretations are possible, we prefer the one matching the
 *  syntactic form, but otherwise the form does not matter.
 *
 *  Hence, both cases come through here.  If fn is null, we're dealing with
 *  column syntax not function syntax.  In the function-syntax case,
 *  the FuncCall struct is needed to carry various decoration that applies
 *  to aggregate and window functions.
[...]

Re: Additional accessors via the Extension API ?

[Markur Sens]

> On 20 Feb 2022, at 12:12 PM, Julien Rouhaud  wrote:
> 
> Hi,
> 
> On Sun, Feb 20, 2022 at 08:07:20AM +0200, Markur Sens wrote:
>> Suppose  I have defined an additional type in a PG extension.
>> 
>> Is it possible to add custom accessors to that type -much like jsonb does-
>> but use an API/hook without touching the core PG grammar & parser?
> 
> Unfortunately no.
> 
>> Hypothetical Examples:
>> 
>> Assuming I have a TextFile type I’d like to implement syntax like:
>> 
>> (‘/home/me/a.txt’::TextFile).firstline
>> (‘/home/me/a.txt’::TextFile).lastline
>> (‘/home/me/a.txt’::TextFile).countlines()
>> (‘/home/me/a.txt’::TextFile).size()
>> (‘/home/me/a.txt’::TextFile).datemodified()
> 
> Maybe you could rely on some old grammar hack to have something a bit similar,
> as (expr).funcname is an alias for funcname(expr).  For instance:

Is this documented & expected behavior or it’s just happens to work?

> 
> # create function f1(int) returns text as $$
> begin
> return 'val: ' || $1::text;
> end;
> $$ language plpgsql;
> 
> # create table t as select 1 as id;
> 
> # select (5).f1, (id).f1 from t;
>   f1   |   f1
> +
> val: 5 | val: 1
> (1 row)
> 
> I don't know if that would be enough for you needs.  Otherwise, the only 
> option
> would be tocreate an operator instead, like mytype -> 'myaccessor' or 
> something
> like that.


Yes, that’s what I’m doing at the moment:
Syntax like type -> ‘accessor’ is pretty straight forward to implement as an 
operator as the rightarg is text.

Things get more complicating as I’m adding support for
mytype -> function(arg=1) 

for that case I have to create an intermediate type of function(arg) so that I 
can then define the left and right args for the -> operator.
But it’s a lot of boilerplate code.

Re: Additional accessors via the Extension API ?

[Julien Rouhaud]

Hi,

On Sun, Feb 20, 2022 at 08:07:20AM +0200, Markur Sens wrote:
> Suppose  I have defined an additional type in a PG extension.
>
> Is it possible to add custom accessors to that type -much like jsonb does-
> but use an API/hook without touching the core PG grammar & parser?

Unfortunately no.

> Hypothetical Examples:
>
> Assuming I have a TextFile type I’d like to implement syntax like:
>
> (‘/home/me/a.txt’::TextFile).firstline
> (‘/home/me/a.txt’::TextFile).lastline
> (‘/home/me/a.txt’::TextFile).countlines()
> (‘/home/me/a.txt’::TextFile).size()
> (‘/home/me/a.txt’::TextFile).datemodified()

Maybe you could rely on some old grammar hack to have something a bit similar,
as (expr).funcname is an alias for funcname(expr).  For instance:

# create function f1(int) returns text as $$
begin
return 'val: ' || $1::text;
end;
$$ language plpgsql;

# create table t as select 1 as id;

# select (5).f1, (id).f1 from t;
   f1   |   f1
+
 val: 5 | val: 1
(1 row)

I don't know if that would be enough for you needs.  Otherwise, the only option
would be tocreate an operator instead, like mytype -> 'myaccessor' or something
like that.

Aw: Additional accessors via the Extension API ?

[Karsten Hilbert]

> Suppose  I have defined an additional type in a PG extension.
> 
> Is it possible to add custom accessors to that type -much like jsonb does- 
> but use an API/hook without touching the core PG grammar & parser? 
> 
> Hypothetical Examples: 
> 
> Assuming I have a TextFile type I’d like to implement syntax like:
> 
> (‘/home/me/a.txt’::TextFile).firstline
> (‘/home/me/a.txt’::TextFile).lastline
> (‘/home/me/a.txt’::TextFile).countlines()
> (‘/home/me/a.txt’::TextFile).size()
> (‘/home/me/a.txt’::TextFile).datemodified()

Off on a tangent but would file_fdw help in any way ?

Karsten