Add support for the `pg` state backend

[iwahbe]

Hello!

• Vote on this issue by adding a 👍 reaction
• If you want to implement this feature, comment to let us know (we'll work with you on design, scheduling, etc.)

Issue details

Details on the backend can be found in https://developer.hashicorp.com/terraform/language/backend/pg.

Affected area/feature