Обсуждение: cloudNativePg bootstrap from dump
Hi
[I'm not sure if cloudNativePg questions are suitable for this list, let me know if not...]
I'd like to bootstrap a Cluster in CNPG from a PostgreSQL dump but I'm not sure it can be done and in case how.
Docs show 3 options: 1. initdb,2. pg_basebackup and 3. recovery
Initdb seems the correct one to me and I could just start an empty db and psql data into it.
When I do that data seem to to get in but the psql process never ends:
kubectl cnpg psql my-db < dump.sql
kubectl cnpg psql my-db < dump.sql
As a second option I'd like to know how to bake a backup from a Postgres instance suitable for the 'recovery' option.
If I understand this options would require me to stick to the same PostgreSQL version/
If I backup a db (kubectl cnpg backup) I get a directory with data.tar.gz (the physical backup) and a JSON file that describes it.
I guess the data are produced by pg_basebackup, bu how should I produce the json description?
sandro
Is it possible that it is not stuck, but simply processing rows of a large table? Try with the -e option, then you'll see.
Il giorno ven 10 mag 2024 alle ore 17:49 Scott Ribe <scott_ribe@elevated-dev.com> ha scritto:
Is it possible that it is not stuck, but simply processing rows of a large table? Try with the -e option, then you'll see.
I'd say no. based on the fact tat If I query the dimension with \l+ I see 85 MB, that is exactly the same dim I find in a db initialized with the same dump. where the import finishes correctly (not in k8s).
On the other side If I add -e and look at the issued SQL statement, many create table statement are missing (but the table are there).
On the other side If I add -e and look at the issued SQL statement, many create table statement are missing (but the table are there).
> On May 10, 2024, at 10:15 AM, Alessandro Dentella <sandro.dentella@gmail.com> wrote: > > I'd say no. based on the fact tat If I query the dimension with \l+ I see 85 MB, that is exactly the same dim I find ina db initialized with the same dump. where the import finishes correctly (not in k8s). > > On the other side If I add -e and look at the issued SQL statement, many create table statement are missing (but the tableare there). I think -e only gives you DML, not DDL. For that you need --echo-all Maybe it's just not closing the connection as you expect when it hits end of file? Maybe something about how that cnf commandworks??? Sitting there waiting for another command?
On Fri, May 10, 2024 at 12:16 PM Alessandro Dentella <sandro.dentella@gmail.com> wrote:
Il giorno ven 10 mag 2024 alle ore 17:49 Scott Ribe <scott_ribe@elevated-dev.com> ha scritto:Is it possible that it is not stuck, but simply processing rows of a large table? Try with the -e option, then you'll see.I'd say no. based on the fact tat If I query the dimension with \l+ I see 85 MB, that is exactly the same dim I find in a db initialized with the same dump. where the import finishes correctly (not in k8s).
On the other side If I add -e and look at the issued SQL statement, many create table statement are missing (but the table are there).
iotop and pg_stat_activity will tell you what is (or is not) happening.
Thanks for pointing out that, but alas no news.
pg_stat_activity shows exactly the same rows as before the import, I also looked at pg_locks, that show nothing new.
Iotop doesn't show anything relevant to me.
Here is the output from postgres, but I think it doesn't say anything interesting
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+-----------------+---------+----------+-----------
relation | 5 | 12073 | | | | | | | | 3/155 | 104 | AccessShareLock | t | t |
virtualxid | | | | | 3/155 | | | | | 3/155 | 104 | ExclusiveLock | t | t |
(2 rows)
postgres=# select * from pg_stat_activity ;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
-------+----------+-----+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+----------------------------------+------------------------------
| | 26 | | | | | | | | 2024-05-11 11:14:05.407952+00 | | | | Activity | AutoVacuumMain | | | | | | autovacuum launcher
| | 28 | | 10 | postgres | | | | | 2024-05-11 11:14:05.408858+00 | | | | Activity | LogicalLauncherMain | | | | | | logical replication launcher
5 | postgres | 104 | | 10 | postgres | psql | | | -1 | 2024-05-11 11:14:07.77121+00 | 2024-05-11 11:17:19.589227+00 | 2024-05-11 11:17:19.589227+00 | 2024-05-11 11:17:19.58923+00 | | | active | | 1192 | | select * from pg_stat_activity ; | client backend
| | 23 | | | | | | | | 2024-05-11 11:14:05.395614+00 | | | | Activity | BgWriterHibernate | | | | | | background writer
| | 27 | | | | | | | | 2024-05-11 11:14:05.408225+00 | | | | Activity | ArchiverMain | | | | | | archiver
| | 22 | | | | | | | | 2024-05-11 11:14:05.39521+00 | | | | Activity | CheckpointerMain | | | | | | checkpointer
| | 25 | | | | | | | | 2024-05-11 11:14:05.407708+00 | | | | Activity | WalWriterMain | | | | | | walwriter
(7 rows)
postgres=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+-----------------+---------+----------+-----------
relation | 5 | 12073 | | | | | | | | 3/155 | 104 | AccessShareLock | t | t |
virtualxid | | | | | 3/155 | | | | | 3/155 | 104 | ExclusiveLock | t | t |
(2 rows)
postgres=# select * from pg_stat_activity ;
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
-------+----------+-----+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+----------------------------------+------------------------------
| | 26 | | | | | | | | 2024-05-11 11:14:05.407952+00 | | | | Activity | AutoVacuumMain | | | | | | autovacuum launcher
| | 28 | | 10 | postgres | | | | | 2024-05-11 11:14:05.408858+00 | | | | Activity | LogicalLauncherMain | | | | | | logical replication launcher
5 | postgres | 104 | | 10 | postgres | psql | | | -1 | 2024-05-11 11:14:07.77121+00 | 2024-05-11 11:17:19.589227+00 | 2024-05-11 11:17:19.589227+00 | 2024-05-11 11:17:19.58923+00 | | | active | | 1192 | | select * from pg_stat_activity ; | client backend
| | 23 | | | | | | | | 2024-05-11 11:14:05.395614+00 | | | | Activity | BgWriterHibernate | | | | | | background writer
| | 27 | | | | | | | | 2024-05-11 11:14:05.408225+00 | | | | Activity | ArchiverMain | | | | | | archiver
| | 22 | | | | | | | | 2024-05-11 11:14:05.39521+00 | | | | Activity | CheckpointerMain | | | | | | checkpointer
| | 25 | | | | | | | | 2024-05-11 11:14:05.407708+00 | | | | Activity | WalWriterMain | | | | | | walwriter
(7 rows)
Il giorno ven 10 mag 2024 alle ore 19:45 Ron Johnson <ronljohnsonjr@gmail.com> ha scritto:
On Fri, May 10, 2024 at 12:16 PM Alessandro Dentella <sandro.dentella@gmail.com> wrote:Il giorno ven 10 mag 2024 alle ore 17:49 Scott Ribe <scott_ribe@elevated-dev.com> ha scritto:Is it possible that it is not stuck, but simply processing rows of a large table? Try with the -e option, then you'll see.I'd say no. based on the fact tat If I query the dimension with \l+ I see 85 MB, that is exactly the same dim I find in a db initialized with the same dump. where the import finishes correctly (not in k8s).
On the other side If I add -e and look at the issued SQL statement, many create table statement are missing (but the table are there).iotop and pg_stat_activity will tell you what is (or is not) happening.
While creating a user in PostgreSQL 15 getting ERROR: require a VALID UNTIL option
От
Naveed Iftikhar
Дата:
Hi Experts,
I just trying to create a user in postgres and getting following errors
postgres=#create user svc_dba;
ERROR: require a VALID UNTIL option
postgres=#create user svc_dba VALID UNTIL 'Jan 31 2030';
ERROR: require a VALID UNTIL option with a date beyond 120 days
How can I just get rid of this VALID UNTIL option b'coz in past I have created user with just 'create user xxxx; and it worked. Please suggest
Best Regards
-Naveed-
Re: While creating a user in PostgreSQL 15 getting ERROR: require a VALID UNTIL option
От
Julien Rouhaud
Дата:
Hi, On Sun, May 12, 2024 at 05:44:04AM GMT, Naveed Iftikhar wrote: > Hi Experts, > I just trying to create a user in postgres and getting following errors > postgres=#create user svc_dba;ERROR: require a VALID UNTIL option > postgres=#create user svc_dba VALID UNTIL 'Jan 31 2030';ERROR: require a VALID UNTIL option with a date beyond 120 days > How can I just get rid of this VALID UNTIL option b'coz in past I have created user with just 'create user xxxx; and itworked. Please suggest You (or your DBA) apparently configured the credcheck extension which prevents you from creating a role without those requirements, so you have to either comply with the requirements or remote the extension.
Re: While creating a user in PostgreSQL 15 getting ERROR: require a VALID UNTIL option
От
Naveed Iftikhar
Дата:
Hi Julien,
Thanks, I think previous DBA has created credcheck extension. How can I remove or bypass or create the user with valid until .. even I provide the valid until but still getting the error.
Best Regards
-Naveed-
On Saturday, May 11, 2024 at 11:01:04 PM PDT, Julien Rouhaud <rjuju123@gmail.com> wrote:
Hi,
On Sun, May 12, 2024 at 05:44:04AM GMT, Naveed Iftikhar wrote:
> Hi Experts,
> I just trying to create a user in postgres and getting following errors
> postgres=#create user svc_dba;ERROR: require a VALID UNTIL option
> postgres=#create user svc_dba VALID UNTIL 'Jan 31 2030';ERROR: require a VALID UNTIL option with a date beyond 120 days
> How can I just get rid of this VALID UNTIL option b'coz in past I have created user with just 'create user xxxx; and it worked. Please suggest
You (or your DBA) apparently configured the credcheck extension which prevents
you from creating a role without those requirements, so you have to either
comply with the requirements or remote the extension.
On Sun, May 12, 2024 at 05:44:04AM GMT, Naveed Iftikhar wrote:
> Hi Experts,
> I just trying to create a user in postgres and getting following errors
> postgres=#create user svc_dba;ERROR: require a VALID UNTIL option
> postgres=#create user svc_dba VALID UNTIL 'Jan 31 2030';ERROR: require a VALID UNTIL option with a date beyond 120 days
> How can I just get rid of this VALID UNTIL option b'coz in past I have created user with just 'create user xxxx; and it worked. Please suggest
You (or your DBA) apparently configured the credcheck extension which prevents
you from creating a role without those requirements, so you have to either
comply with the requirements or remote the extension.
Re: While creating a user in PostgreSQL 15 getting ERROR: require a VALID UNTIL option
От
Julien Rouhaud
Дата:
On Sun, May 12, 2024 at 06:04:36AM GMT, Naveed Iftikhar wrote: > Hi Julien, > Thanks, I think previous DBA has created credcheck extension. How can I remove or bypass or create the user with validuntil .. even I provide the valid until but still getting the error. You should look at the documentation for the exact syntax to use: https://github.com/MigOpsRepos/credcheck
Julien Rouhaud <rjuju123@gmail.com> writes: > On Sun, May 12, 2024 at 06:04:36AM GMT, Naveed Iftikhar wrote: >> Thanks, I think previous DBA has created credcheck extension. How can I remove or bypass or create the user with validuntil .. even I provide the valid until but still getting the error. > You should look at the documentation for the exact syntax to use: > https://github.com/MigOpsRepos/credcheck This is pretty rich, because not only is credcheck making up this valid-until date on its own, but then it's objecting to what it made up. 'Jan 31 2030' is a perfectly valid date according to Postgres, and "require a VALID UNTIL option with a date beyond 120 days" is not an error that appears anywhere in core Postgres, even if we somehow failed to do the implied date arithmetic correctly. I think your answer is to remove that extension, preferably with gloves and tongs. regards, tom lane
Re: While creating a user in PostgreSQL 15 getting ERROR: require a VALID UNTIL option
От
Naveed Iftikhar
Дата:
Thanks a lot Julien.
Best Regards
-Naveed-
On Saturday, May 11, 2024 at 11:10:16 PM PDT, Julien Rouhaud <rjuju123@gmail.com> wrote:
On Sun, May 12, 2024 at 06:04:36AM GMT, Naveed Iftikhar wrote:
> Hi Julien,
> Thanks, I think previous DBA has created credcheck extension. How can I remove or bypass or create the user with valid until .. even I provide the valid until but still getting the error.
You should look at the documentation for the exact syntax to use:
https://github.com/MigOpsRepos/credcheck
> Hi Julien,
> Thanks, I think previous DBA has created credcheck extension. How can I remove or bypass or create the user with valid until .. even I provide the valid until but still getting the error.
You should look at the documentation for the exact syntax to use:
https://github.com/MigOpsRepos/credcheck