0

I have Samba and Postgres installed on two machines.

I can successfully copy files from one machine to another when fhe files' user and group are my sign-in user and group for both machines. (The same sign in and password on both machines.)

My target directory, /mnt/pg-ralph12/, has been successfully mounted.

mount: //192.168.29.205/pg-ralph12 mounted on /mnt/pg-ralph12


The user and group for my Postgres directory on my source machine is:

getfacl: Removing leading '/' from absolute path names # file: var/lib/postgresql # owner: postgres # group: postgres user::rwx group::r-x other::r-x


For my target directory it's

getfacl: Removing leading '/' from absolute path names # file: mnt/pg-ralph12/not-main # owner: real-owner # group: sudo user::rwx group::r-x group:postgres:rwx mask::rwx other::r-x default:user::rwx default:group::r-x default:group:postgres:rwx default:mask::rwx default:other::r-x

When I attempt to run rsync

sudo rsync -a /var/lib/postgresql /mnt/pg-ralph12/not-main

I get 

[sudo] password for real-estate-data-mining: rsync: chown "/mnt/pg-ralph12/not-main/postgresql" failed: Permission denied (13) rsync: chown "/mnt/pg-ralph12/not-main/postgresql/9.6" failed: Permission denied (13) rsync: chown "/mnt/pg-ralph12/not-main/postgresql/9.6/main" failed: Permission denied (13) rsync: chown "/mnt/pg-ralph12/not-main/postgresql/9.6/main/base" failed: Permission denied (13) etc

I've searched high and low on the 'Net trying to find an answer with no success.

Improve this question
1

1 Answer 1

Reset to default
0

The solution I came up with is the following bash script:

#!/bin/bash # Copy/replace myDatabase from Ralph07 to Ralph12 dropdb -e -h 192.168.29.205 --no-password --if-exists -U postgres myDatabase # createdb -e -h 192.168.29.205 --no-password -U postgres myDatabase # The database will automagically be created pg_dump -C -h localhost --no-password -U postgres myDatabase | psql -h 192.168.29.205 --no-password -U postgres

192.168.29.205 is the (static) ip address of the target computer. Your ip will likely be different.

In order to get the --no-password option to work, you'll have to edit ~/.pgpass. Mine (edited!) looks like the following:

localhost:5432:*:postgres:MyPassword 192.168.29.205:5432:*:postgres:MyPassword

See, for instance, https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html for an explanation of .pgpass

You'll probably also need to edit /etc/postgresql/9.6/main/postgresql.conf (if you're using 9.6 of Postgres). See https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

This solution seems to be robust. I've had no trouble with it in the short time I've been using it (less than a day). If I run into problems, I'll report it here.

Other solutions I've seen online to drop the database (i.e. going into psql) did not work for me.

What did _not_ work for me

For reference, the following did NOT work for me to drop the database.

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'myDatabase'; SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'myDatabase' AND pid <> pg_backend_pid(); DROP DATABASE "myDatabase";

I get the error "ERROR: cannot drop the currently open database"

Further question:

What's the difference between dropdb and the code immediately above?

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.