Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X
Team I followed Greg suggested steps .One of big had only one table and around four million recordsi am doing dev env restoration into new vmthe target VM env is an POC server and took 3 hrs to restore four million records.Now it is doing process of lo_open / lo_close / lowrite etci.e pg-dump-creates-a-lot-of-pg-catalog-statements is there any alternate way , to speedup this process. i can see in the select count(*) record count is matching (target and source) Regards On Wednesday, December 4, 2024 at 10:47:26 AM EST, Greg Sabino Mullane wrote: On Wed, Dec 4, 2024 at 7:42 AM Bharani SV-forum wrote: a) is the above said steps is correct with the given existing and proposed setup No. Here are some steps: * Install Postgres on the new VMHowever you get it, use the newest version you can. As of this writing, it is Postgres 17.2. Version 15 is okay, but going to 17 now means a better Postgres today, and no worrying about replacing v15 in three years. * Create a new Postgres clusterOn the new VM, use the initdb command to create a new data directory.Use the --data-checksums option * Start it upAdjust your postgresql.conf as neededAdjust your pg_hba.conf as neededInstall any extensions used on the old VMStart the cluster using the pg_ctl command (or systemctl) * Test connection to the old vm from the new vmOn the new vm, see if you can connect to the old one:psql -h oldvm -p 5432 --listYou may need to adjust firewalls and pg_hba.conf on the old vm. * Copy the dataRun this on the new VM, adjusting ports as needed:time pg_dumpall -h oldvm -p 5432 | psql -p 5432 Bonus points for doing this via screen/tmux to prevent interruptions * Generate new statistics and vacuumOn the new vm, run:psql -c 'vacuum freeze'psql -c 'analyze' * Test your application * Setup all the other stuff (systemd integration, logrotate, cronjobs, etc.) as needed As Peter mentioned earlier, this can be done without disrupting anything, and is easy to test and debug. The exact steps may vary a little, as I'm not familiar with how Amazon Linux packages Postgres, but the basics are the same. Take it slow. Go through each of these steps one by one. If you get stuck or run into an issue, stop and solve it, reaching out to this list as necessary. Cheers,Greg
Re: Initial Postgres admin account setup using Ansible?
> > On Tue, Dec 31, 2024 at 10:32 PM Nick wrote: > > > > I'm trying to create an Ansible playbook that sets up and manages > > Postgres on Debian 12. > > > > I'm having issues with the default username/login structure, and > > could > > use some help. > > > > I'm installing the `postgresql` package via apt, and Debian creates > > a > > `postgres` system account that has a locked password. > > > > I can login to Postgres manually by first becoming root then > > running > > `sudo -u postgres psql` as root. But when the Ansible user (which > > has > > passwordless sudo) tries to run `sudo -u postgres psql`, I get: > > > > "Sorry, user Ansible is not allowed to execute '/usr/bin/psql' as > > postgres on example.com." > > > > This is likely because the postgres POSIX account has a locked > > password, so only root can become postgres. Other users with sudo > > permissions can't become a locked account. > > > > So I **could** unlock the `postgres` POSIX account, but I > > understand > > that this account is locked for a reason. > > > > The goal is to have Ansible manage the creation of databases and > > roles > > in the Postgres database. > > > > So I need to create an account in Postgres that Ansible can use as > > the > > super user. I would like to do this in a way that doesn't require > > me to > > manually login to the server, become root, become postgres as root, > > then manually create an Ansible role. > > > > What is the proper (secure) way to let the Ansible POSIX user > > manage > > postgres? It seems there should be a fully automated way to > > bootstrap > > an Ansible user for `postgres`. > > > I think I found a working solution: In `pg_hba.conf`, change: ``` local all postgrespeer ``` to: ``` localall all peer map=ansible_map ``` In `pg_ident.conf`, add: ``` ansible_map ansible postgres ansible_map postgrespostgres ``` Then in the playbook, don't become (stay as `ansible`): ``` - name: Ping PostgreSQL postgresql_ping: db: postgres login_unix_socket: "/var/run/postgresql" login_user: postgres become: false ``` This seems to work, but is it secure? If USER is `all` in `pg_hba.conf`, can any POSIX account login?
Re: Initial Postgres admin account setup using Ansible?
On Tue, Dec 31, 2024 at 5:17 PM Nick wrote: > > ``` > localall all peer map=ansible_map > ``` > > > In `pg_ident.conf`, add: > > ``` > ansible_map ansible postgres > ansible_map postgrespostgres > > ``` > > > This seems to work, but is it secure? If USER is `all` in > `pg_hba.conf`, can any POSIX account login? > > The presence of the mapping file reference makes the entry secure in the sense that only those connection combinations that are explicitly permitted can happen. The "all" is automatically restricted to those accounts listed in the file. At worst you might get an unwanted failure if, say, you wanted some other account "alice" to be able to connect to the cluster using the role "alice". The "all" would match and use the mapping that doesn't include "alice". David J.
Initial Postgres admin account setup using Ansible?
I'm trying to create an Ansible playbook that sets up and manages Postgres on Debian 12. I'm having issues with the default username/login structure, and could use some help. I'm installing the `postgresql` package via apt, and Debian creates a `postgres` system account that has a locked password. I can login to Postgres manually by first becoming root then running `sudo -u postgres psql` as root. But when the Ansible user (which has passwordless sudo) tries to run `sudo -u postgres psql`, I get: "Sorry, user Ansible is not allowed to execute '/usr/bin/psql' as postgres on example.com." This is likely because the postgres POSIX account has a locked password, so only root can become postgres. Other users with sudo permissions can't become a locked account. So I **could** unlock the `postgres` POSIX account, but I understand that this account is locked for a reason. The goal is to have Ansible manage the creation of databases and roles in the Postgres database. So I need to create an account in Postgres that Ansible can use as the super user. I would like to do this in a way that doesn't require me to manually login to the server, become root, become postgres as root, then manually create an Ansible role. What is the proper (secure) way to let the Ansible POSIX user manage postgres? It seems there should be a fully automated way to bootstrap an Ansible user for `postgres`.
Re: Initial Postgres admin account setup using Ansible?
On Dec 31, 2024, at 13:31, Nick wrote: > What is the proper (secure) way to let the Ansible POSIX user manage > postgres? It seems there should be a fully automated way to bootstrap > an Ansible user for `postgres`. This is generally done with "become" and "become_user" in a shell command, something like: - name: Do something as the postgres user ansible.builtin.shell: "psql ..." register: pgbackrest_which_output become: true become_user: postgres
Re: Initial Postgres admin account setup using Ansible?
Hello, On Tue, Dec 31, 2024 at 10:32 PM Nick wrote: > > I'm trying to create an Ansible playbook that sets up and manages > Postgres on Debian 12. > > I'm having issues with the default username/login structure, and could > use some help. > > I'm installing the `postgresql` package via apt, and Debian creates a > `postgres` system account that has a locked password. > > I can login to Postgres manually by first becoming root then running > `sudo -u postgres psql` as root. But when the Ansible user (which has > passwordless sudo) tries to run `sudo -u postgres psql`, I get: > > "Sorry, user Ansible is not allowed to execute '/usr/bin/psql' as > postgres on example.com." > > This is likely because the postgres POSIX account has a locked > password, so only root can become postgres. Other users with sudo > permissions can't become a locked account. > > So I **could** unlock the `postgres` POSIX account, but I understand > that this account is locked for a reason. > > The goal is to have Ansible manage the creation of databases and roles > in the Postgres database. > > So I need to create an account in Postgres that Ansible can use as the > super user. I would like to do this in a way that doesn't require me to > manually login to the server, become root, become postgres as root, > then manually create an Ansible role. > > What is the proper (secure) way to let the Ansible POSIX user manage > postgres? It seems there should be a fully automated way to bootstrap > an Ansible user for `postgres`. > Can you please provide an example of the task(s) which fail? If you have passwordless "sudo" configured tor the user running Ansible, this works: - name: Ping PostgreSQL postgresql_ping: db: postgres login_unix_socket: "/var/run/postgresql" login_user: postgres become: yes become_user: postgres More examples and details: https://andreas.scherbaum.la/writings/Managing_PostgreSQL_with_Ansible_-_Percona_Live_2022.pdf Regards, -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project
Re: Initial Postgres admin account setup using Ansible?
On Tue, 2024-12-31 at 23:16 +0100, Andreas 'ads' Scherbaum wrote: > > > > Can you please provide an example of the task(s) which fail? > If you have passwordless "sudo" configured tor the user running > Ansible, > this works: > > - name: Ping PostgreSQL > postgresql_ping: > db: postgres > login_unix_socket: "/var/run/postgresql" > login_user: postgres > become: yes > become_user: postgres > > More examples and details: > https://andreas.scherbaum.la/writings/Managing_PostgreSQL_with_Ansible_-_Percona_Live_2022.pdf > > When trying this: - name: Ping PostgreSQL postgresql_ping: db: postgres login_unix_socket: "/var/run/postgresql" login_user: postgres become: yes become_user: postgres I get: Ping PostgreSQL... xxx.xxx.xxx.xxx failed | msg: Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chmod: invalid mode: ‘A+user:postgres:rx:allow’ Try 'chmod --help' for more information. }). For information on working around this, see https://docs.ansible.com/ansible-core/2.17/playbook_guide/playbooks_privilege_escalation.html#risks-of-becoming-an-unprivileged-user
