Thread: Streaming replication issue

On Thu, Sep 5, 2019 at 11:39 AM mallikarjun t <mallit333@gmail.com> wrote:
> ERROR: psql.bin: FATAL: role "postgres" is not permitted to log in

I don't know about EDB, but sounds like role postgres has not the
login privilege, that is they use another administrator?

> ./psql -p 5432
>
> psql.bin: could not connect to server: No such file or directory
>
>                Is the server running locally and accepting connections on unix domain socket.
>
>               "/tmp/.s.PGSQL.5432"?

Without the -h localhost option psql tries to connect to unix socket,
not TCP/IP, and it could be your server is not listening on unix
socket.

Luca

I tried different ways it is not connected, but streaming replication is happening. IAM happily connected to master server, not the slave server, what reason I don't know? I think postgres user don't have login permission on Edb advanced server , by default Enterprise db user is there. That's why it's not connected I am thinking. Any one know about this one please clarify the issue.

Regards,

Malli.

On Thu, Sep 5, 2019, 3:26 PM Luca Ferrari <fluca1978@gmail.com> wrote:

On Thu, Sep 5, 2019 at 11:39 AM mallikarjun t <mallit333@gmail.com> wrote:
> ERROR: psql.bin: FATAL: role "postgres" is not permitted to log in

I don't know about EDB, but sounds like role postgres has not the
login privilege, that is they use another administrator?

> ./psql -p 5432
>
> psql.bin: could not connect to server: No such file or directory
>
>                Is the server running locally and accepting connections on unix domain socket.
>
>               "/tmp/.s.PGSQL.5432"?

Without the -h localhost option psql tries to connect to unix socket,
not TCP/IP, and it could be your server is not listening on unix
socket.

Luca

On Thu, Sep 5, 2019 at 12:45 PM mallikarjun t <mallit333@gmail.com> wrote:
>
> This is for testing purposes I am checking, we can do streaming replication between same server version is possible,
butEDB Advanced servers is possible or not? I have doubt, but it is possible but these are the error messages are
comingto connected. How to resolve this issue? 

I think it is possible, but you better ask someone at EDB for details.
Just looking at the configuration of the EBD instance you should guess
on which port/sockets it is listening for incoming connections.
Also, when you installed the EDB instance, did you get any advice
about administrator user (may be you changed from postgres to
somethink else)?
Please take into account this is not a replication issue at all, it is
a more generic low-level problem.

Luca

On Thu, Sep 5, 2019 at 1:54 PM mallikarjun t <mallit333@gmail.com> wrote:
> I tried these two compatibilities I will get the same issue.

According to the installation guide available at

<https://www.enterprisedb.com/edb-docs/static/docs/epas/11/EDB_Postgres_Advanced_Server_Installation_Guide_Linux_v11.pdf>
the database to connect to is 'edb' with user 'enterprisedb'.
So does
psql -U enterprisedb edb
allow you to connect to your cluster?

Luca

Hello,

Please, you could check postgresql.log
or What is this : ./psql  -p 5444 -d edb -U enterprisedb.?

El 2019-09-05 08:45, mallikarjun t escribió:
> Dear  All,
> 
> Before streaming replication I will access EDB Advanced  Server below
> command by using psql
> 
> ./psql  -p 5444 -d edb -U enterprisedb.
> 
> I am happily working.
> 
> When I am starting replication process stop the server and take the
> basebackup of PostgreSQL Community version and start the new cluster.
> 
> for example
> 
> /opt/edb/as9.6/bin/pg_basebackup  -D  /home/postgres/data_96  -h
> <master ip address>  -p 5432  -X stream -R
> 
> I am  taking basebackup then  after recovery.conf file I include
> trigger_file ='/tmp/primary_down',
> 
> If my master is down unexpectedly, I will promote my Slave to Master.
> 
> the after I start the server by using following commnad
> 
> /opt/edb/as9.6/bin/pg_ctl    -D    /home/postgres/data_96  start
> 
> server started and wal receiver is working properly, but  real problem
> is happens here only,
> 
> I want connect to sever by using following command
> /opt/edb/as9.6/bin/psql
> 
> role "postgres" not a log in permission error message  I am getting ,
> I am try to put port number along with that
> error: could not connect to the server, locally running this port
> number 5432.
> 
> this is the error message I am getting.
> 
> Regards,
> Malli.
> 
> On Thu, Sep 5, 2019 at 5:58 PM Luca Ferrari <fluca1978@gmail.com>
> wrote:
> 
>> On Thu, Sep 5, 2019 at 1:54 PM mallikarjun t <mallit333@gmail.com>
>> wrote:
>>> I tried these two compatibilities I will get the same issue.
>> 
>> According to the installation guide available at
>> 
>
<https://www.enterprisedb.com/edb-docs/static/docs/epas/11/EDB_Postgres_Advanced_Server_Installation_Guide_Linux_v11.pdf>
>> the database to connect to is 'edb' with user 'enterprisedb'.
>> So does
>> psql -U enterprisedb edb
>> allow you to connect to your cluster?
>> 
>> Luca

El 2019-09-05 09:39, mallikarjun t escribió:
> Dear Gilberto,
> 
> ./psql  -p 5444 -d edb -U enterprisedb this one is accessing the EDB
> Advanced Server by using this command.
> Iam already checked the logs role "postgres" is not a login permission
> error message is there.

Do you check it?
/opt/edb/as9.6/bin/pg_ctl -p 5444   -D    /home/postgres/data_96  start

> 
> Regards,
> Malli.
> 
> On Thu, Sep 5, 2019 at 6:35 PM <gilberto.castillo@etecsa.cu> wrote:
> 
>> Hello,
>> 
>> Please, you could check postgresql.log
>> or What is this : ./psql  -p 5444 -d edb -U enterprisedb.?
>> 
>> El 2019-09-05 08:45, mallikarjun t escribió:
>>> Dear  All,
>>> 
>>> Before streaming replication I will access EDB Advanced  Server
>> below
>>> command by using psql
>>> 
>>> ./psql  -p 5444 -d edb -U enterprisedb.
>>> 
>>> I am happily working.
>>> 
>>> When I am starting replication process stop the server and take
>> the
>>> basebackup of PostgreSQL Community version and start the new
>> cluster.
>>> 
>>> for example
>>> 
>>> /opt/edb/as9.6/bin/pg_basebackup  -D  /home/postgres/data_96  -h
>>> <master ip address>  -p 5432  -X stream -R
>>> 
>>> I am  taking basebackup then  after recovery.conf file I include
>>> trigger_file ='/tmp/primary_down',
>>> 
>>> If my master is down unexpectedly, I will promote my Slave to
>> Master.
>>> 
>>> the after I start the server by using following commnad
>>> 
>>> /opt/edb/as9.6/bin/pg_ctl    -D    /home/postgres/data_96  start
>>> 
>>> server started and wal receiver is working properly, but  real
>> problem
>>> is happens here only,
>>> 
>>> I want connect to sever by using following command
>>> /opt/edb/as9.6/bin/psql
>>> 
>>> role "postgres" not a log in permission error message  I am
>> getting ,
>>> I am try to put port number along with that
>>> error: could not connect to the server, locally running this port
>>> number 5432.
>>> 
>>> this is the error message I am getting.
>>> 
>>> Regards,
>>> Malli.
>>> 
>>> On Thu, Sep 5, 2019 at 5:58 PM Luca Ferrari <fluca1978@gmail.com>
>>> wrote:
>>> 
>>>> On Thu, Sep 5, 2019 at 1:54 PM mallikarjun t
>> <mallit333@gmail.com>
>>>> wrote:
>>>>> I tried these two compatibilities I will get the same issue.
>>>> 
>>>> According to the installation guide available at
>>>> 
>>> 
>> 
>
<https://www.enterprisedb.com/edb-docs/static/docs/epas/11/EDB_Postgres_Advanced_Server_Installation_Guide_Linux_v11.pdf>
>>>> the database to connect to is 'edb' with user 'enterprisedb'.
>>>> So does
>>>> psql -U enterprisedb edb
>>>> allow you to connect to your cluster?
>>>> 
>>>> Luca

On Thu, Sep 5, 2019 at 2:45 PM mallikarjun t <mallit333@gmail.com> wrote:
> I want connect to sever by using following command  /opt/edb/as9.6/bin/psql
>
>
> role "postgres" not a log in permission error message  I am getting , I am try to put port number along with that
> error: could not connect to the server, locally running this port number 5432.
>

I'm sorry but it is quite hard to understand what you are doing.
If I'm getting right, from you description, you are replicating your
server and trying to connect as 'postgres' to such clone, but how can
the clone has a user that did not existed (or did not have
permissions) on the original server too?
I thinm you should connect to the "new master" with the same "old
master" credentials.

Luca

On Thu, Sep 5, 2019 at 5:54 PM John Wiencek <jwiencek3@comcast.net> wrote:
>
> H
>
> When you set up streaming replication your read-only replica should be created using pg_basebackup.
>
> for example;
>
> Run this from the streamed/slave server.   Set variable $PGDATA.  Make sure directory structure exits and is empty(
nofile in $PGDATA on slave server) 
>
> pg_basebackup --pgdata=$PGDATA --label=standby --host 10.64.98.199--usernamerepuser --wal-method=stream
--write-recovery-conf.
>
>
> Your PGDATA direetory on the streamed/salve server needs to be empty.   The basebackup will copy ALL files from the
masterto the slave.   This would include PostgreSQL.conf , pg_hba.conf and all associated files for the databases. 
>
> Therefore the streaming/slave server will be EXACTLY the same as the master.   Same users, same port, same data.
>

I am under impression that what you are trying to do is merging two
Primary databases. If that is what you actually want then Streaming
Replication is not going to work for you. Maybe you should look for
some Multi-master replication tool, which is probably the way to go if
you want to mix PostgreSQL and EDB Advanced Server anyway.

Regards,

Juan José Santamaría Flecha

Multi master requires that you identify one node as the primary node.   Data is populated to the remote node via an
initialsnapshot.   Tables on the remote node cannot exist when you run the initial snapshot.  The snapshot will create
allobjects on the remote node .   


xDB(multi-master replication) will not merge two databases together.

Sent from my iPad

> On Sep 5, 2019, at 11:08 AM, Juan José Santamaría Flecha <juanjo.santamaria@gmail.com> wrote:
>
>> On Thu, Sep 5, 2019 at 5:54 PM John Wiencek <jwiencek3@comcast.net> wrote:
>>
>> H
>>
>> When you set up streaming replication your read-only replica should be created using pg_basebackup.
>>
>> for example;
>>
>> Run this from the streamed/slave server.   Set variable $PGDATA.  Make sure directory structure exits and is empty(
nofile in $PGDATA on slave server) 
>>
>> pg_basebackup --pgdata=$PGDATA --label=standby --host 10.64.98.199--usernamerepuser --wal-method=stream
--write-recovery-conf.
>>
>>
>> Your PGDATA direetory on the streamed/salve server needs to be empty.   The basebackup will copy ALL files from the
masterto the slave.   This would include PostgreSQL.conf , pg_hba.conf and all associated files for the databases. 
>>
>> Therefore the streaming/slave server will be EXACTLY the same as the master.   Same users, same port, same data.
>>
>
> I am under impression that what you are trying to do is merging two
> Primary databases. If that is what you actually want then Streaming
> Replication is not going to work for you. Maybe you should look for
> some Multi-master replication tool, which is probably the way to go if
> you want to mix PostgreSQL and EDB Advanced Server anyway.
>
> Regards,
>
> Juan José Santamaría Flecha

On Thu, Sep 5, 2019 at 6:23 PM John Wiencek <jwiencek3@comcast.net> wrote:
>
> Multi master requires that you identify one node as the primary node.   Data is populated to the remote node via an
initialsnapshot.   Tables on the remote node cannot exist when you run the initial snapshot.  The snapshot will create
allobjects on the remote node . 
>
>
> xDB(multi-master replication) will not merge two databases together.
>
> >
> > I am under impression that what you are trying to do is merging two
> > Primary databases. If that is what you actually want then Streaming
> > Replication is not going to work for you. Maybe you should look for
> > some Multi-master replication tool, which is probably the way to go if
> > you want to mix PostgreSQL and EDB Advanced Server anyway.
> >

By "you" I meant Mallikarjun and "merge" was meant to be as generic as
possible, since I do not have a clear picture of the problem.

Maybe if the original poster comments on the topic we can give him
better advice.

Regards,

Juan José Santamaría Flecha

On Thu, Sep 5, 2019 at 8:21 PM mallikarjun t <mallit333@gmail.com> wrote:
>
> By main intension is doing streaming replication between postgres community version to edb advanced version. I am
tryingIAM achieve something streaming replication But I didn't access slave server that the problem.
 

You must access the slave with the same credentials you use for the
master. Once replication is on its way, it does replicate _everything_
in the cluster, including users.
Can you connect to the master with a user and then connect to the
slave with the same user? If not, what are the errors you get?