[GENERAL] Build in function to verify email addresses

[Nick Dro]

I beleieve that every information system has the needs to send emails.
Currently PostgreSQL doesn't have a function which gets TEXT and return true if it's valid email address (x...@yyy.com / .co.ZZ)
Do you believe such function should exist in PostgreSQL or it's best to let every user to implement his own function?

Re: [GENERAL] expanded auto and header linestyle double

[Pavel Stehule]

Hi

2017-11-15 23:37 GMT+01:00 Matt Zagrabelny :

> Greetings,
>
> Using PG 10.1.
>
> In my .psqlrc I have:
>
> \x auto
> \pset linestyle 'unicode'
> \pset unicode_header_linestyle double
>
> and when the output is expanded, I do not see a double line for the first
> record, but I do for all subsequent records. For example:
>
> % select  * from artist;
> ─[ RECORD 1 ]─
> artistid │ 1
> name │ AC/DC
> ═[ RECORD 2 ]═
> artistid │ 2
> name │ Accept
> ═[ RECORD 3 ]═
> artistid │ 3
> name │ Aerosmith
> ═[ RECORD 4 ]═
> artistid │ 4
> name │ Alanis Morissette
> ═[ RECORD 5 ]═
> artistid │ 5
>
> I would like to have the initial "RECORD 1" line have the same "double"
> linestyle as the other records.
>
> Am I missing a config item?
>

yes - it is border line

use \pset border 2

and you understand

you are missing

\pset unicode_border_linestyle double

Regards

Pavel


> Thanks!
>
> -m
>

Re: [GENERAL] pg_basebackup running from a remote machine

[Jeff Janes]

On Tue, Nov 14, 2017 at 8:28 AM, Laurenz Albe 
wrote:

> rakeshkumar464 wrote:
> > If pg_basebackup is run from a remote machine with compress option
> --gzip ,
> > compress level 9,
> > will the compression occur prior to the data being sent on the network or
> > after it has been received
> > at the remote machine.
>
> That only means that the output TAR file will be compressed, it has
> nothing to do with the data transfered from the server.
>
> If you want to compress the data sent over the network, use
> pg_basebackup over an SSL connection with SSL compression enabled.
>

But ssl compression is disabled by default on most systems, and not even
supported at all on many without doing a custom compilation of the ssl
library.

Cheers,

Jeff

Re: [GENERAL] Setting up replication slave on remote high latency host

[Rory Falloon]

Thank you for that. Back to the drawing board!

On Wed, Nov 15, 2017 at 9:30 PM, John R Pierce  wrote:

> On 11/15/2017 6:02 PM, Rory Falloon wrote:
>
>>
>> Right now I am trying to dump the database, gzip, move across, and import
>> into the new slave (which is configured as a master to perform the initial
>> setup). Ideally I do this dump, move and import during a period of
>> inactivity on the master so the new server will come up and immediately be
>> able to catch up on replication due to lack of activity. However, I have
>> been importing the current db as a test and after 90 minutes it seems to
>> have only got 2/3 of the way. I am not confident this will work but it
>> seems like the most efficient way to start.
>>
>
>
> you can't use pg_dump to create a slave, as it won't have the same
> timeline.
>
> I would use pg_basebackup, but in general streaming replication over a
> high latency erratic link will never work real well.
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Setting up replication slave on remote high latency host

[John R Pierce]

On 11/15/2017 6:02 PM, Rory Falloon wrote:


Right now I am trying to dump the database, gzip, move across, and 
import into the new slave (which is configured as a master to perform 
the initial setup). Ideally I do this dump, move and import during a 
period of inactivity on the master so the new server will come up and 
immediately be able to catch up on replication due to lack of 
activity. However, I have been importing the current db as a test and 
after 90 minutes it seems to have only got 2/3 of the way. I am not 
confident this will work but it seems like the most efficient way to 
start.



you can't use pg_dump to create a slave, as it won't have the same timeline.

I would use pg_basebackup, but in general streaming replication over a 
high latency erratic link will never work real well.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Setting up replication slave on remote high latency host

[Rory Falloon]

Hi,

Thoughts and opinions on this please -

I have a db (data dir is 90gb) that I am trying to setup on a replication
slave. The slave is on a host which maintains latency over 300ms at all
times (wan link).

Other times I have done this setup, I have simply rsync'ed the data dir to
another host, set config, ran rsync again and fired up the slave. this
works well.

However, my bandwidth to the host in question fluctuates between 800k/sec
to 3MB/sec. Performing this initial rsync and then having to rsync again if
the replication slave drops out due to network latency is not something I
think is going to work in this situation.

Right now I am trying to dump the database, gzip, move across, and import
into the new slave (which is configured as a master to perform the initial
setup). Ideally I do this dump, move and import during a period of
inactivity on the master so the new server will come up and immediately be
able to catch up on replication due to lack of activity. However, I have
been importing the current db as a test and after 90 minutes it seems to
have only got 2/3 of the way. I am not confident this will work but it
seems like the most efficient way to start.

Have I missed anything here?

Now, assuming I get the slave up, how best can I mitigate the slave from
dropping out because of latency and being able to recover? Increasing the
amount of wal segments would be the best way, correct?

Thanks,

Re: [GENERAL] PGPool in Master-Master mode, is it possible?

[Tatsuo Ishii]

I am not familiar with swarm cluster nor repmgr. So I wonder why you
wouldn't be able to get to the goal you are trying to achieve without
repmgr. Can you please elaborate more?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> I am also interested in this set-up
> My idea is to run pgpool in a docker swarm cluster and to have two instances 
> of pgpool on different nodes (but not in watchdog mode). In this case it is 
> not possible to give the responsablity to pgpool to do the failover otherwise 
> both instances would try to do the failover (they don't know about each 
> other, no watchdog mode). So I have to set failover_command parameter to an 
> empty string and let repmgr do the automatic failover. I have tested and I 
> believe it is ok: in case of master failure repmgr does the promote, pgpool 
> will not do the failover but will constantly try to reconnect to a new master 
> and as soon as repmgr has done the promotion it is OK.
> The important think is that when pgpool starts I have to read the state of 
> the cluster in the repmgr metadata table (repl_nodes) and generate the 
> /tmp/pgpool_status file based on that. (I even have to do a promote_node in 
> the edge case that the master is down and was brought down when pgpool also 
> was down).  
> But the inconvenient is that if pgpool fails, let's say because the docker 
> node of pgpool is stopped, then docker swarm restart the service on another 
> node but this can take a few seconds. So I would prefer to have multiple 
> pgpool. I did not have the time right now to test such a scenario but I am 
> interested to know if it is possible.
> Pierre 
> 
> On Wednesday, November 15, 2017, 11:02:31 AM GMT+1, Vikas Sharma 
>  wrote:  
>  
>  Thank you Tatsuo for the Reply, I will post this in list you mentioned.
> 
> By Master-Master, I meant two pgpool servers both active accepting 
> connections from Application, If one Pgpool Server becomes unlavailable other 
> still accepting connections. At an Ideal time, both Pgpool instances on each 
> PgPool server working actively.  The database in this case will be 1 master 
> and 2 slaves, replicated by streaming replication.
> 
> Regards
> VS
> 
> On 14 November 2017 at 22:56, Tatsuo Ishii  wrote:
> 
>> Hello There,
>>
>> I need to setup two PGPool Servers in Master-Master mode, First thing I
>> want to know, is it possible?
>>
>> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
>> http://www.pgpool.net/pgpool- web/contrib_docs/watchdog_ 
>> master_slave_3.3/en.html
>>
>> Could anyone please enlighten me and any workaround for this?
>>
>> Regards
>> VS
> 
>> I need to setup two PGPool Servers in Master-Master mode, First thing I
>> want to know, is it possible?
>>
>> I know we can setup 2 PGPool servers in master-slave mode using watchdog.
>> http://www.pgpool.net/pgpool- web/contrib_docs/watchdog_ 
>> master_slave_3.3/en.html
>>
>> Could anyone please enlighten me and any workaround for this?
> 
> This is not an appropriate list. You should go to the Pgpool mailing
> list:
> 
> https://www.pgpool.net/ mailman/listinfo/pgpool- general
> 
> Anyway... I am not sure what you mean by "Master-Master mode" but if
> that means you want to issue write queries by connecting to any of
> Pgpool-II port, it's already possible in the set up you have created.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_ en.php
> Japanese:http://www.sraoss.co. jp
> 
> 
>   


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] expanded auto and header linestyle double

[Matt Zagrabelny]

Greetings,

Using PG 10.1.

In my .psqlrc I have:

\x auto
\pset linestyle 'unicode'
\pset unicode_header_linestyle double

and when the output is expanded, I do not see a double line for the first
record, but I do for all subsequent records. For example:

% select  * from artist;
─[ RECORD 1 ]─
artistid │ 1
name │ AC/DC
═[ RECORD 2 ]═
artistid │ 2
name │ Accept
═[ RECORD 3 ]═
artistid │ 3
name │ Aerosmith
═[ RECORD 4 ]═
artistid │ 4
name │ Alanis Morissette
═[ RECORD 5 ]═
artistid │ 5

I would like to have the initial "RECORD 1" line have the same "double"
linestyle as the other records.

Am I missing a config item?

Thanks!

-m

Re: [GENERAL] problem changing jsonb attribute to null value

[Dmitry Dolgov]

> On 15 November 2017 at 22:54, RODRIGUEZ CORTES MARIO IGNACIO <
ignacio.cor...@inegi.org.mx> wrote:
>
> I have a problem with a record in a jsonb type table, I'm trying to
> change the value of an attribute to null but it leaves me all the
> content in null and not just the value
>
> prueba=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}', to_jsonb(null));
> ERROR:  no se pudo determinar el tipo polimórfico porque el tipo de
> entrada es «unknown»
>
> defining null value as a text type:
>
> prueba=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}',
> to_jsonb(null::text));
>  jsonb_set
> ---
>
> (1 fila)
>
> it leaves the record in null, when I hope it leaves it with the null
> value in attribute "v": {"v": null}

I think something like this should work:

=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}', 'null'::jsonb);
  jsonb_set
-
 {"v": null}
(1 row)

is that what you want?

[GENERAL] problem changing jsonb attribute to null value

[RODRIGUEZ CORTES MARIO IGNACIO]

Hello:

I have a problem with a record in a jsonb type table, I'm trying to
change the value of an attribute to null but it leaves me all the
content in null and not just the value, I show you an example first of
how to define the null attribute:

prueba=# select jsonb_build_object('v', null);
 jsonb_build_object 

 {"v": null}
(1 fila)

this jsonb record is correct, then when trying to change the value with
jsonb_set with some value of type jsonb, it does it correctly:

prueba=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}', to_jsonb(1));
 jsonb_set 
---
 {"v": 1}
(1 fila)

but if i try to put the null value in "v" attribute, apparently is not
allowed:

prueba=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}', to_jsonb(null));
ERROR:  no se pudo determinar el tipo polimórfico porque el tipo de
entrada es «unknown»

defining null value as a text type:

prueba=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}',
to_jsonb(null::text));
 jsonb_set 
---
 
(1 fila)

it leaves the record in null, when I hope it leaves it with the null
value in attribute "v": {"v": null}


thank you for your help

Regards
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup running from a remote machine

[rakeshkumar464]

Does pg_basebackup on a remote machine follow the standard libpq protocol.  I
am not able to force it to use ssl, despite having an entry in pg_hba.conf:

hostnossl all all all reject

>From the same remote machine, psql is forced to use ssl.

Makes me wonder whether pg_basebackup has a different protocol.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Query on pg_settings view

[Stephen Frost]

Greetings,

* Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> Ronen Nofar wrote:
> > I have a weird case when running a query on  the pg_settings view.
> > I have two users, first one is the default user - postgres which is a 
> > superuser
> > and another one is a role which i had created, i called it test_role and 
> > it's not a superuser.
> > When I run a select on pg_settings with these two users I recieve different 
> > results.
> 
> That is intentional, because some settings should only be
> visible for superusers, for example everything that has to
> do with the operating system (location of configuration file
> or socket directories).

This isn't quite correct any longer- with PG10, we have a default role
called 'pg_read_all_settings' which can be GRANT'd to other roles to
allow viewing of all settings, even those previously restricted to
superuser-only.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Query on pg_settings view

[Laurenz Albe]

Ronen Nofar wrote:
> I have a weird case when running a query on  the pg_settings view.
> I have two users, first one is the default user - postgres which is a 
> superuser
> and another one is a role which i had created, i called it test_role and it's 
> not a superuser.
> When I run a select on pg_settings with these two users I recieve different 
> results.

That is intentional, because some settings should only be
visible for superusers, for example everything that has to
do with the operating system (location of configuration file
or socket directories).

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PGPool in Master-Master mode, is it possible?

[Vikas Sharma]

Thank you Tatsuo for the Reply, I will post this in list you mentioned.

By Master-Master, I meant two pgpool servers both active accepting
connections from Application, If one Pgpool Server becomes unlavailable
other still accepting connections. At an Ideal time, both Pgpool instances
on each PgPool server working actively.  The database in this case will be
1 master and 2 slaves, replicated by streaming replication.

Regards
VS

On 14 November 2017 at 22:56, Tatsuo Ishii  wrote:

> > Hello There,
> >
> > I need to setup two PGPool Servers in Master-Master mode, First thing I
> > want to know, is it possible?
> >
> > I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> > http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_
> master_slave_3.3/en.html
> >
> > Could anyone please enlighten me and any workaround for this?
> >
> > Regards
> > VS
>
> > I need to setup two PGPool Servers in Master-Master mode, First thing I
> > want to know, is it possible?
> >
> > I know we can setup 2 PGPool servers in master-slave mode using watchdog.
> > http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_
> master_slave_3.3/en.html
> >
> > Could anyone please enlighten me and any workaround for this?
>
> This is not an appropriate list. You should go to the Pgpool mailing
> list:
>
> https://www.pgpool.net/mailman/listinfo/pgpool-general
>
> Anyway... I am not sure what you mean by "Master-Master mode" but if
> that means you want to issue write queries by connecting to any of
> Pgpool-II port, it's already possible in the set up you have created.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>

[GENERAL] Query on pg_settings view

[Ronen Nofar]

Hi,

I have a weird case when running a query on  the pg_settings view.
I have two users, first one is the default user - postgres which is a
superuser and another one is a role which i had created, i called it
test_role and it's not a superuser.
When I run a select on pg_settings with these two users I recieve different
results.
Running the query 'select * from pg_settings' with 'postgres' user returns
269 rows when running the same query with 'test_role' returns 254 rows.
For example, the following query 'select lower(setting) from pg_settings
where lower(name) = 'config_file'' returns null when I connect with
'test_role' but with 'postgres' user I recieve one row
(/var/lib/pgsql/9.6/data/postgresql.conf).

I have granted all privileges on pg_settings to 'test_role' but it didn't
changed the results.

I don't have any idea what it's wrong here.

The PostgreSQL version is PostgreSQL 9.6.1 on x86_64-pc-linux-gnu

Can you please help me with this issue?

Thanks in advance,
Ronen