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.

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 | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.