How to specify a password with shp2pgsql using CentOS?

Question

I'm developing a python script that uses shp2sql via subprocess.call().

The string I'm using works fine on Linux Mint:

shp2pgsql -s 4326 ../data/download.shp ccd_hoods_new | psql -h hostname -d databasename -U username -P password -q

However, when I run the script on CentOS, I'm prompted for a password, which presents a new problem when deploying and automating via CRON.

The only documentation I can find is this cheatsheet, which confirms that I'm doing things correctly.

But why on the CentOS machine would it not be recognizing the -P flag?

Both servers are running the same script that points to the same host running PostgreSQL 9.6 and PostGIS 2.4.

Answer 1

The -P argument to psql is not used to supply a password; see the documentation at this link.

You can supply a password to psql using one of three methods:

1. Enter the password at a prompt
2. Enter the password in your ~/.pgpass file
3. Set the PGPASSWORD environment variable

Maybe your Mint system has the password defined using #2 or #3? If not, perhaps the server has disabled authentication when connecting from certain IP addresses.

EDIT by @DPSSpatial

2. works by constructing the following command:

   shp2pgsql -s 4326 ../data/download.shp ccd_hoods_new | PGPASSWORD=password psql -h hostname -d database -U username -q
   

I found this syntax from this post: https://stackoverflow.com/a/24953448/1704448