Re: Both type of replications from a single server?

[Srinivasa T N]

On Thu, Oct 8, 2020 at 12:19 PM Michael Paquier  wrote:

> On Thu, Oct 08, 2020 at 11:43:26AM +0530, Srinivasa T N wrote:
> >Is it possible to have both type of replications (streaming and
> logical)
> > from a single server?
>
> Yes.
>
> >If I have 3 servers A,B and C, then I want to have streaming
> replication
> > from A to B whereas logical replication from A to C.  Is it possible?
>
> And yes.
>
My next question is "Are there any setup instructions available for the
same"?

Regards,
Seenu.

pgadmin4 error

[Levente Birta]

Hello,

I use pgadmin4 with server installation, on Centos7, installed from 
official postgresql yum repo.


Since v4.24, IIRC, I got error when I try to visualize the SQL for a 
table: INTERNAL SERVER ERROR ... expected token 'end of statement 
block', got '.'


For views, foreign tables or any other stuff is working ...

Any ideas?
Thanks
Levi


The apache error log:

[Thu Oct 08 10:29:05.260331 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] 2020-10-08 10:29:05,259: ERROR\tflask.app:\texpected 
token 'end of statement block', got '.'
[Thu Oct 08 10:29:05.260347 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] Traceback (most recent call last):
[Thu Oct 08 10:29:05.260350 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/flask/app.py", line 1813, 
in full_dispatch_request
[Thu Oct 08 10:29:05.260352 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] rv = self.dispatch_request()
[Thu Oct 08 10:29:05.260353 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/flask/app.py", line 1799, 
in dispatch_request
[Thu Oct 08 10:29:05.260361 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] return 
self.view_functions[rule.endpoint](**req.view_args)
[Thu Oct 08 10:29:05.260363 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/flask/views.py", line 88, 
in view
[Thu Oct 08 10:29:05.260365 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] return self.dispatch_request(*args, **kwargs)
[Thu Oct 08 10:29:05.260367 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/pgadmin/browser/utils.py", 
line 313, in dispatch_request
[Thu Oct 08 10:29:05.260369 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] return method(*args, **kwargs)
[Thu Oct 08 10:29:05.260371 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py", 
line 158, in wrap
[Thu Oct 08 10:29:05.260373 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] return f(*args, **kwargs)
[Thu Oct 08 10:29:05.260374 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py", 
line 1388, in sql
[Thu Oct 08 10:29:05.260376 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] self, did=did, scid=scid, tid=tid, 
main_sql=main_sql, data=data)
[Thu Oct 08 10:29:05.260378 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py", 
line 770, in get_reverse_engineered_sql
[Thu Oct 08 10:29:05.260380 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] self._get_resql_for_table(did, scid, tid, data, 
json_resp, main_sql)
[Thu Oct 08 10:29:05.260382 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py", 
line 495, in _get_resql_for_table
[Thu Oct 08 10:29:05.260383 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] data=data, conn=self.conn, is_sql=True)
[Thu Oct 08 10:29:05.260385 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/flask/templating.py", 
line 134, in render_template
[Thu Oct 08 10:29:05.260387 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] return 
_render(ctx.app.jinja_env.get_or_select_template(template_name_or_list),
[Thu Oct 08 10:29:05.260388 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/jinja2/environment.py", 
line 851, in get_or_select_template
[Thu Oct 08 10:29:05.260390 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] return self.get_template(template_name_or_list, 
parent, globals)
[Thu Oct 08 10:29:05.260392 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/jinja2/environment.py", 
line 812, in get_template
[Thu Oct 08 10:29:05.260393 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] return self._load_template(name, 
self.make_globals(globals))
[Thu Oct 08 10:29:05.260395 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/python3.6/site-packages/pgadmin4-web/jinja2/environment.py", 
line 786, in _load_template
[Thu Oct 08 10:29:05.260397 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105] template = self.loader.load(self, name, globals)
[Thu Oct 08 10:29:05.260398 2020] [wsgi:error] [pid 1047] [remote 
ip.ad.re.ss:53105]   File 
"/usr/lib/

Re: Both type of replications from a single server?

[Srinivasa T N]

On Thu, Oct 8, 2020 at 12:50 PM Srinivasa T N  wrote:

> On Thu, Oct 8, 2020 at 12:19 PM Michael Paquier 
> wrote:
>
>> On Thu, Oct 08, 2020 at 11:43:26AM +0530, Srinivasa T N wrote:
>> >Is it possible to have both type of replications (streaming and
>> logical)
>> > from a single server?
>>
>> Yes.
>>
>> >If I have 3 servers A,B and C, then I want to have streaming
>> replication
>> > from A to B whereas logical replication from A to C.  Is it possible?
>>
>> And yes.
>>
> My next question is "Are there any setup instructions available for the
> same"?
>

For streaming replication, I need to set wal_level to replica in A whereas
for logical_replication we need to set wal_level to replica in the same A
server.  So, was wondering how to go about?

Regards,
Seenu.

>

Re: Both type of replications from a single server?

[Michael Paquier]

On Thu, Oct 08, 2020 at 01:25:14PM +0530, Srinivasa T N wrote:
> For streaming replication, I need to set wal_level to replica in A whereas
> for logical_replication we need to set wal_level to replica in the same A
> server.  So, was wondering how to go about?

A logical replica needs wal_level = logical, a setting that also
allows to do streaming replication for a physical replica.  But the
opposite is not true, as using wal_level = replica will not work for
logical replicas.  So, assuming that you want to have both logical and
physical replicas that replicate from the same source server, you need
to set wal_level to logical on the primary server because it is a
system-wide configuration.
--
Michael


signature.asc
Description: PGP signature

Re: How to migrate column type from uuid to serial

[Francisco Olarte]

On Thu, Oct 8, 2020 at 6:14 AM Hemil Ruparel  wrote:
> I was thinking UUID was not a very good choice for id. Serial would be a 
> better one because I don't have a billion customers. It is more like a 
> thousand. So when I saw the customer ID of the payment gateway cannot accept 
> more than 32 characters, I thought UUID is overkill. So I want to migrate to 
> using a serial int instead as the primary key.,

I do not like them. They are typically random 128 bit integers. If you
think of it, random 16 bits ints are not a good idea for keys, even if
the table has only a couple dozen keys. UUIDs are the same at a bigger
scale, they look nicer and safer, but have got problems. The version
1-2, the one with mac address with theoretically guaranteed uniqueness
were nice for their intended use, avoiding collisions without a
central authority. But the namespace hash and random version are
practically collision free, but not theoretically, which displeases
me. When I need to present an obscured name to an outside service (
i.e., in your case, presenting the user id to the gateway directly
leaks information, as they can estimate your consumer growth ) I just
encrypt ( not hash, encrypt ) them. For these you can do something
like treating a 64 bit number like an 8 byte array, encrypt it with a
"secret" key and you send something which is random enough to the
gateway, but is easy for you to manage in the database, and preserves
the counter niceties, like knowing there are approximately 1000
customers between IDs 3000 and 4000 ( some may have been deleted
/skiped ). Also, depending on your patterns, IDs typically correlate
nicely with table order, which makes index access a bit faster, you do
not jump around. And 4 byte ints make for a much denser index than 16
byte uuid.

Francisco Olarte.

Does the work made by vaccum in the current pass is lost when interrupted?

[Jean-Marc Lessard]

I have a large table (billions of records) which has not been vacuum and 
bloated.
Vacuum scale factor was left at the default.

I ran a vacuum on a DEV system and it makes several passes (scanning 
heap/vacuuming indexes/vacumming heap) which take more than an hour each.
On a PROD system, I may have to kill the job midway.
Should I reduce the autovacuum_work_mem of my session? Currently 1GB
Does the work made in the current pass is lost when interrupted?
Correct me if I am not right, but vacuumed indexes and heap made by the current 
pass will go to disk, but the heap should be rescan on the next vacuum.
I guess that the visibility map is updated only at the end of each pass.

My best regards

Thank you


Jean-Marc Lessard
Administrateur de base de données / Database Administrator

[cid:image001.jpg@01D69D5F.64DB0EA0]

Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

Version10.8 startup issue

[Udaya Deepthi]

Hi all

Please help me to fix the start up issues.

I want postgresql to start when server reboots on linux 7.6 environment-Rhel

Systemctl start postgresql-10 will turn on but not while reboot.

Postgres 9.4.5 starts during startup .
Please help


Thanks
Deepthi

Re: Version10.8 startup issue

[Tom Lane]

Udaya Deepthi  writes:
> I want postgresql to start when server reboots on linux 7.6 environment-Rhel
> Systemctl start postgresql-10 will turn on but not while reboot.
> Postgres 9.4.5 starts during startup .

You probably want to read about "systemctl enable" and "systemctl disable".

regards, tom lane

Re: Version10.8 startup issue

[Adrian Klaver]

On 10/8/20 2:19 PM, Udaya Deepthi wrote:

Hi all

Please help me to fix the start up issues.

I want postgresql to start when server reboots on linux 7.6 environment-Rhel

Systemctl start postgresql-10 will turn on but not while reboot.


If you installed from the community repos see bottom of this page:

https://www.postgresql.org/download/linux/redhat/


Postgres 9.4.5 starts during startup .
Please help


Thanks
Deepthi



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

Re: Gurjeet Singh Index Adviser User Interface

[Yessica Brinkmann]

 Goodnight,
I apologize please that today I was able to test the directory, since I had
a health problem in recent days.
Well, I actually found the files corresponding to the pg_advise_index
interface. But I was surprised that the executable is not present, but
apparently this interface must be compiled by another part of the index
adviser, to be able to execute it. It has a Makefile.
I tried to compile as follows, and I get the following errors:
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
USE_PGXS = 1 make
You need to install postgresql-server-dev-X.Y for building a server-side
extension or libpq-dev for building a client-side application.
make: *** There are no targets. High.
root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
make install
make: Nothing is done for 'install'.
I will greatly appreciate a help please, regarding these errors that I got.
The truth is that I honestly  don't have much experience using Linux.
Best regards,
Yessica brinkmann


Libre
de virus. www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann (<
brinkmann.yess...@gmail.com>) escribió:

> Goodnight,
> Thank you very much for the answer.
> I followed all the installation instructions: apply patch, compile
> install postgres, etc.
> I just tried everything with the UI ii) Manually (through psql session),
> which is also mentioned in the readme, and everything works for me. But
> to better test my thesis, I would also need to use the other user interface
> mentioned in the readme and that is precisely i) pg_advise_index tool.
> I understand what you are telling me about the directory. I will be
> testing this way.
> Many thanks.
> Best regards,
> Yessica brinkmann
>
> El mar., 29 sept. 2020 a las 18:21, Rob Sargent ()
> escribió:
>
>>
>>
>> On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
>> > I will greatly appreciate a help with this topic please. I really need
>> > to use that interface to be able to test my thesis. And I am not being
>> > able to use.
>> > Best regards,
>> > Yessica Brinkmann
>>
>> I didn't see any reported error messages in your post.
>>
>> And you followed all the installation instructions: apply patch, compile
>> install postgres, etc?  Not for the faint of heart, to be sure.
>>
>> As presented
>>
>> pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql
>>
>> would need to be run in the directory containing advisory.sql and your
>> PATH would need to include the directory containing the
>> 'pg_advise_index' executable.
>>
>>
>>
>>
>>
>
> 
>  Libre
> de virus. www.avg.com
> 
> <#m_-6212125912420465024_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

Re: Gurjeet Singh Index Adviser User Interface

[Yessica Brinkmann]

 I also clarify that I tried to install the libpq-dev package already but
could not install it because it depends on a non-installable package called
libss10.9.8. And I had to apply apt --fix-broken install to fix the problem.


Libre
de virus. www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

El jue., 8 oct. 2020 a las 22:08, Yessica Brinkmann (<
brinkmann.yess...@gmail.com>) escribió:

> Goodnight,
> I apologize please that today I was able to test the directory, since I
> had a health problem in recent days.
> Well, I actually found the files corresponding to the pg_advise_index
> interface. But I was surprised that the executable is not present, but
> apparently this interface must be compiled by another part of the index
> adviser, to be able to execute it. It has a Makefile.
> I tried to compile as follows, and I get the following errors:
> root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
> USE_PGXS = 1 make
> You need to install postgresql-server-dev-X.Y for building a server-side
> extension or libpq-dev for building a client-side application.
> make: *** There are no targets. High.
> root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
> make install
> make: Nothing is done for 'install'.
> I will greatly appreciate a help please, regarding these errors that I
> got. The truth is that I honestly  don't have much experience using Linux.
> Best regards,
> Yessica brinkmann
>
>
> 
>  Libre
> de virus. www.avg.com
> 
> <#m_-2818491497838977803_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann (<
> brinkmann.yess...@gmail.com>) escribió:
>
>> Goodnight,
>> Thank you very much for the answer.
>> I followed all the installation instructions: apply patch, compile
>> install postgres, etc.
>> I just tried everything with the UI ii) Manually (through psql session),
>> which is also mentioned in the readme, and everything works for me. But
>> to better test my thesis, I would also need to use the other user interface
>> mentioned in the readme and that is precisely i) pg_advise_index tool.
>> I understand what you are telling me about the directory. I will be
>> testing this way.
>> Many thanks.
>> Best regards,
>> Yessica brinkmann
>>
>> El mar., 29 sept. 2020 a las 18:21, Rob Sargent ()
>> escribió:
>>
>>>
>>>
>>> On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
>>> > I will greatly appreciate a help with this topic please. I really need
>>> > to use that interface to be able to test my thesis. And I am not being
>>> > able to use.
>>> > Best regards,
>>> > Yessica Brinkmann
>>>
>>> I didn't see any reported error messages in your post.
>>>
>>> And you followed all the installation instructions: apply patch, compile
>>> install postgres, etc?  Not for the faint of heart, to be sure.
>>>
>>> As presented
>>>
>>> pg_advise_index -d DB -h host -U user -s 10M -o advisory.sql workload.sql
>>>
>>> would need to be run in the directory containing advisory.sql and your
>>> PATH would need to include the directory containing the
>>> 'pg_advise_index' executable.
>>>
>>>
>>>
>>>
>>>
>>
>> 
>>  Libre
>> de virus. www.avg.com
>> 
>> <#m_-2818491497838977803_m_-6212125912420465024_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>>
>

rum index supported on pg13?

[John the Scott]

will rum index from postgrespro be supported in pg13?
numerous errors occur when compiling rum in pg13 and
no replies from github.  the differences from pg12
to pg13 seem to be significant

 https://github.com/postgrespro/rum

thanks for such a brilliant extension.
perhaps i should jump in and help.

-john


-- 
Fast is fine, But accuracy is final.
You must learn to be slow in a hurry.
- Wyatt Earp