Unable to Connect to a PostgreSQL Database Named "postgres" with ArcGIS Pro and arcpy

Question

hello:

I'm trying to connect to a PostgreSQL database named "postgres", but ArcGIS Pro and arcpy don't seem to like this name.

Is there an alternative?

I don't want to create another database named "toto" 😅, for example, nor synchronization processes, etc. Here is the code I'm using as well as the error I'm getting:

arcpy.management.CreateDatabaseConnection(
        out_folder_path=OUT_FOLDER_PATH,
        out_name=OUT_NAME,
        database_platform="POSTGRESQL",
        instance=INSTANCE,
        account_authentication=ACCOUNT_AUTHENTICATION,
        username=USERNAME,
        password=PASSWORD,
        save_user_pass =SAVE_USER_PASS,
        database='postgres',
    )

000800: The value is not a member of <value>.

VinceAngelo · Answer

It's not exactly best practice to make use of the master database for anything GIS. In fact, it's pretty close to worst practice. It wouldn't surprise if it is not permitted (and if it is, I would file a Defect to have that loophole closed).

Pretty much any training in database technology is going to tell you to create a new logical storage, a database or databases to house your data (leveraging the storage), and additional user logins and group logins, and schemas in the databases to match and/or correspond to the logins.

Creating user data in reserved admin databases presents an unnecessary risk to database stability.  Creating a "toto" database for your non-Kansas data would in fact be best practice, and I encourage you to do so.

- V

Asrujit_SenGupta · Answer

Are you able to connect to other PostgreSQL databases? The documentation doesn't mention any restrictions w.r.t the database name, other than it should be in lowercase.

Not really a Python expert, but I tried creating a simple connection and copy the associated code. See if this helps:

arcpy.management.CreateDatabaseConnection(
    out_folder_path=r"C:	emp",
    out_name="test.sde",
    database_platform="POSTGRESQL",
    instance="hostname",
    account_authentication="DATABASE_AUTH",
    username="username",
    password="password",
    save_user_pass="SAVE_USERNAME",
    database="database_name",
    schema="",
    version_type="TRANSACTIONAL",
    version="",
    date=None,
    auth_type="",
    project_id="",
    default_dataset="",
    refresh_token=None,
    key_file=None,
    role="",
    warehouse="",
    advanced_options=""
)