I'm in the process of moving our postgres databases to AWS. The database is currently installed on the server itself and using version 9.6.
The database on AWS has been set to use 15. I've updated the connection credentials but am getting this error:
SQLSTATE[08006] [7] authentication method 10 not supported
From what I understand it's because my server had and older version which is using md5 encryption instead of scram.
But now that the database will be on AWS instead of the server, do I still need to install postgres on the server, or is it sufficient to change the ecryption method from md5 to scram.
Just trying to understand how this connection works. Thanks.
If PHP is installed on the same machine as the old database is, then it is probably loading the client library shared object file (libpq.so) that was installed along with the 9.6 database server software. That means upgrading it could be a challenge, because doing so will likely try to upgrade your old database server software, which could be a mess if you are not done migrating it yet. It is possible to have different versions live side by side, but it can be complicated and is probably not worth the effort.
The cleanest solution might be to just move your php scripts to a new machine where it will no longer be entangled with the old PostgreSQL installation. Especially since you should probably be looking to upgrade it anyway.
If that won't work, then if you change the password_encryption on the new server to md5 and reset the password (first making sure the new pg_hba will allow it to work) then PHP on the old server should be able to connect to the new server. Once the old PostgreSQL server is killed off for good, it would then be easier to upgrade the client libraries without causing problems, and you could reverse this change in password encryption method.