Thread: Streaming Replication Configuration

I'm attempting today to get streaming replication from the Wiki
configured on my two 9.1.2 servers:
http://wiki.postgresql.org/wiki/Streaming_Replication

I'm on step #6

6. Make a base backup by copying the primary server's data directory
to the standby server.

$ psql -c "SELECT pg_start_backup('label', true)"
$ rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
$ psql -c "SELECT pg_stop_backup()"

When I attempt the 1st command listed on my primary (master) server, I
get this error:

carlos@db1:~$ psql -c "SELECT pg_start_backup('label', true)"
psql: FATAL:  database "carlos" does not exist

Now I'm showing they want me to use the -c switch to connect but no
database is defined so is it implied that I need to add 'postgres'
maintenance database in there or something else?

On 15/12/2011 16:41, Carlos Mennens wrote:
> carlos@db1:~$ psql -c "SELECT pg_start_backup('label', true)"
> psql: FATAL:  database "carlos" does not exist
>
> Now I'm showing they want me to use the -c switch to connect but no
> database is defined so is it implied that I need to add 'postgres'
> maintenance database in there or something else?
>

Yes, depending on your setup you may need both -U <user name> and the
database name also - if neither is specified, psql tries to connect to a
database named after the current OS user, and tries to connect as a user
with the same name as the current OS user also.

So if you're logged in as "carlos", then the above command is the same as:

  psql -U carlos -c "...." carlos

HTH,

Ray.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

 Hi Carlos,

 On Thu, 15 Dec 2011 11:41:07 -0500, Carlos Mennens
 <carlos.mennens@gmail.com> wrote:
> carlos@db1:~$ psql -c "SELECT pg_start_backup('label', true)"
> psql: FATAL:  database "carlos" does not exist

 By default (unless changed at compile time or using environment
 variables), a client connection is instantiated using the system user.
 In your case 'carlos'. Another default setting is to use the same
 database as the username (in your case 'carlos' again).

 This is what is happening in your case.

 For more info, I suggest that you look at the 'Usage' section in this
 page: http://www.postgresql.org/docs/9.1/static/app-psql.html

 Anyway, in order to execute pg_start_backup/pg_stop_backup in 9.1 you
 need replication role (or superuser).

> Now I'm showing they want me to use the -c switch to connect but no
> database is defined so is it implied that I need to add 'postgres'
> maintenance database in there or something else?

 If the user carlos in your database has replication privileges, you can
 try and execute the commands by connecting to the postgres database (if
 your pg_hba.conf file allows you to do that, but that's another issue).

 Ciao,
 Gabriele
--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

 On Thu, 15 Dec 2011 11:41:07 -0500, Carlos Mennens
 <carlos.mennens@gmail.com> wrote:
> carlos@db1:~$ psql -c "SELECT pg_start_backup('label', true)"
> psql: FATAL:  database "carlos" does not exist

 I forgot. You can also look at this:
 http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html

 Ciao,
 Gabriele
--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it

On 15/12/2011 16:41, Carlos Mennens wrote:
> Now I'm showing they want me to use the -c switch to connect but no
> database is defined so is it implied that I need to add 'postgres'
> maintenance database in there or something else?

I meant to add that -c doesn't mean "connect"; instead it specifies an
SQL command to be executed, after which psql exits. Without -c, psql
enters interactive mode.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie