Re: Vacuum very big table - how the full vacuum works in background/internally?
Hello! We solved it. The VACUUM full finished fast on pg_largeobject, because we deleted 98% of big largeobject (ours) before. And it worked as zip deletion - it created a new file and copied only living records, which was fast (3 GB vs. 80 GB). Thanks dd Luca Ferrari ezt írta (időpont: 2019. okt. 17., Cs, 17:43): > On Thu, Oct 17, 2019 at 5:10 PM Durumdara wrote: > > Please help me, how the PGSQL full vacuum works internally? (1., 2. > case, or something else) > > > > As far as I know a VACUUM FULL will rewrite the whole table without > inserting (and therefore deleting later) not visible tuples. I would > be quite surprised of it duplicating the table and removing after the > tuples. > Surely it is a very invasive command that locks the table and requires I/O. > > > How we (and the clients) prepare to this operation? > > We must know it to avoid disk out problems, and too much off-time. > > The best guess is that you are going to need almost the double of the > table size. Since you said that autovacuum is preventing the table to > grow, it could mean all the reclaimed space has been consumed by other > tuples, so I'm not sure vacuum full can provide you space. > Have you looked at pg_stat_user_tables to see the dead tuples fraction? > > Luca >
Upgrade PGSQL main version without backup/restore all databases?
Dear Members! We have PGSQL 9.6.xxx on a Linux server which heavily used. More than 100 databases, and more than 300 active users, and it is a master of a cluster (the data replicated on a slave). Somewhere we have read that 9.6 will become unsupported shortly. We need to prepare upgrade. In Windows test environment I experienced long time ago that new versions installed in different folders, so I can't upgrade the PG and the database simply... I must dump all databases and restor under new PG - what is very wrong. This Linux is heavily used. We can't stop to lock out all users, make a dumpall, upgrade, restore them all in new version, and then leave them to connect (200 GB of data), because it is too long. Is there any way to upgrade PG and databases without backup/restore? Maybe the solution is to upgrade slave without sync the data changes before; and if all is newer, leave to get the new data from the master. I don't know it's possible or not. The newer PG slave could make mistakes if the master have lower PG version... Do you know any idea for this operation? Thank you! Best regards dd
Re: Upgrade PGSQL main version without backup/restore all databases?
Durumdara schrieb am 06.11.2019 um 14:09: > We have PGSQL 9.6.xxx on a Linux server which heavily used. > More than 100 databases, and more than 300 active users, and it is a master > of a cluster (the data replicated on a slave). > > Somewhere we have read that 9.6 will become unsupported shortly. "Shortly" is relative. It will fall out of support in 2021: https://www.postgresql.org/support/versioning/ But it's a good idea to plan the upgrade now. > We can't stop to lock out all users, make a dumpall, upgrade, restore > them all in new version, and then leave them to connect (200 GB of > data), because it is too long. > > Is there any way to upgrade PG and databases without backup/restore? Yes, you can use pg_upgrade. However it will still copy 200GB (but using a filecopy, not dump/restore) so it could still take some time. If you use it with the --link option, the upgrade will be very quick as only the catalog tables need to be copied (export/import). > Maybe the solution is to upgrade slave without sync the data changes > before; and if all is newer, leave to get the new data from the > master. I don't know it's possible or not. The newer PG slave could > make mistakes if the master have lower PG version... There are some ways to do a near-zero upgrade using logical replication, but it's not easy to configure. See this blog post for example: https://www.cybertec-postgresql.com/en/upgrading-postgres-major-versions-using-logical-replication/ Thomas
Re: Hunspell as filtering dictionary
On Tue, 5 Nov 2019 at 09:42, Bibi Mansione wrote: > Hi, > I am trying to create a ts_vector from a French text. Here are the > operations that seem logical to perform in that order: > > 1. remove stopwords > 2. use hunspell to find words roots > 3. unaccent > I can't speak to French, but we use a similar configuration in English, with unaccent first, then hunspell. We found that there were words that hunspell didn't recognise, but instead pulled apart (for example, "contract" became "con" and "tract"), so I wonder if something similar is happening with "découvrir." To solve this, we put a custom dictionary with these terms in front of hunspell. Unaccent definitely has to be called first. We also modified hunspell with a custom stopwords file, to eliminate select other terms, such as profanities: -- We use a custom stopwords file, to filter out other terms, such as profanities ALTER TEXT SEARCH DICTIONARY hunspell_en_ca ( Stopwords = our_custom_stopwords ); -- Adding english_stem allows us to recognize words which hunspell -- doesn't, particularly acronyms such as CGA ALTER TEXT SEARCH CONFIGURATION our_configuration ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part WITH unaccent, our_custom_dictionary, hunspell_en_ca, english_stem ; There was definitely a fair bit of trial and error to determine the correct order and configuration.
Re: Upgrade PGSQL main version without backup/restore all databases?
-k option is kept precisely for this. The upgrades are pretty fast, but still with some downtime. may be 30-45 min tops.
Locked out of schema public
This is FreeBSD 11.3, with postgres installed from ports as 10.10.
There is included a daily utility doing pg_dump:
: ${daily_pgsql_pgdump_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -bF
c"}
pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}
Recently I did a restore of some database, as the postgres user, with:
pg_restore -c -d -h
and now ordinary users are locked out of the database:
PG::UndefinedTable: ERROR: relation "users" does not exist
=> \d users
Did not find any relation named "users".
=> \d
Did not find any relations.
=> \d public.users
Table "public.users"
[etc.etc. all is present]
=> show search_path;
search_path
-
"$user", public
(1 row)
=> select current_schemas(false);
current_schemas
-
{}
(1 row)
eh HOPPALA!!!
=> select * from public.users;
ERROR: permission denied for schema public
How can this happen? I don't think I twiddled anything with schemas,
in fact I never used them in any way.
cheers,
PMc
Re: Locked out of schema public
On 11/6/19 11:11 AM, Peter wrote:
This is FreeBSD 11.3, with postgres installed from ports as 10.10.
There is included a daily utility doing pg_dump:
: ${daily_pgsql_pgdump_args:="-U ${daily_pgsql_user} -p ${daily_pgsql_port} -bF
c"}
pg_dump ${daily_pgsql_pgdump_args} -f ${file} ${db}
What is ${daily_pgsql_user} equal to?
Recently I did a restore of some database, as the postgres user, with:
pg_restore -c -d -h
I am not seeing -U postgres.
Are you sure there is not something else specifying the user e.g. env
PGUSER?
and now ordinary users are locked out of the database:
What user are you doing below as?
What does \dn+ show?
PG::UndefinedTable: ERROR: relation "users" does not exist
=> \d users
Did not find any relation named "users".
=> \d
Did not find any relations.
=> \d public.users
Table "public.users"
[etc.etc. all is present]
=> show search_path;
search_path
-
"$user", public
(1 row)
=> select current_schemas(false);
current_schemas
-
{}
(1 row)
eh HOPPALA!!!
=> select * from public.users;
ERROR: permission denied for schema public
How can this happen? I don't think I twiddled anything with schemas,
in fact I never used them in any way.
cheers,
PMc
--
Adrian Klaver
adrian.kla...@aklaver.com
How to convert return values from JSON Path functions to text
The new JSON path functions in Postgres 12 are really convenient, however I
cannot figure out how to properly convert their return values to a text value.
E.g. select jsonb_path_query_first('{"a": "foo"}', '$.a') returns a JSONB value.
Casting it to text, still yields "foo" (with quotes), rather than foo (without
quotes)
For the time being I am using something like this:
create function jsonb_to_text(p_value jsonb)
returns text
as
$$
select case jsonb_typeof(p_value)
when 'string' then trim('"' from p_value::text)
else p_value::text
end;
$$
language sql
immutable
strict;
But that feels a bit "heavyweight" - I was hoping for an easier (and more
efficient) way to do that.
Thomas
Re: Locked out of schema public (pg_dump lacks backup of the grant)
Long story short: pg_dump just forgets to backup the grant on schema public. :( Long story: After searching for half an hour to get some comprehensive listing of permissions (which was in vain) I tried with pgadmin3 (which is indeed a life-saver and still somehow works on 10.10 - and that's the reason I am reluctant to upgrade postgres, as this can only get worse) - and then it was a simple action of comparing page-by-page: GRANT ALL ON SCHEMA public TO public; That one is missing on the restored database. So, if you do a "pg_restore -C -c -d postgres", then you get that grant from the template database, and no problem. (But this is ugly, as you need to find and terminate all the connections on the db.) If you do only "pg_restore -c -d ", the sessions can stay open, but then it will do DROP SCHEMA public; CREATE SCHEMA public; and it will NOT restore the grant because it is not in the backup. I'd like to call this a bug.
Re: Locked out of schema public (pg_dump lacks backup of the grant)
Peter writes: > If you do only "pg_restore -c -d ", the sessions can stay open, > but then it will do > DROP SCHEMA public; > CREATE SCHEMA public; > and it will NOT restore the grant because it is not in the backup. We improved that situation in v11, I believe. What I see for this case these days is per commit 5955d9341: Also, change the very ad-hoc mechanism that was used to avoid dumping creation and comment commands for the public schema. Instead of hardwiring a test in _printTocEntry(), make use of the DUMP_COMPONENT_ infrastructure to mark that schema up-front about what we want to do with it. This has the visible effect that the public schema won't be mentioned in the output at all, except for updating its ACL if it has a non-default ACL. Previously, while it was normally not mentioned, --clean mode would drop and recreate it, again causing headaches for non-superuser usage. This change likewise makes the public schema less special and more like other built-in objects. regards, tom lane
Re: Locked out of schema public
Hi Adrian,
okay, lets check these out:
> What is ${daily_pgsql_user} equal to?
postgres. The owner of the installation.
> I am not seeing -U postgres.
> Are you sure there is not something else specifying the user e.g. env
> PGUSER?
I'm sure. The log shows the nightly backup connections as
postgres:, and all connections except postgres:postgres work only
with kerberos - it cannot do much bogus there.
> What user are you doing below as?
Ordinary application user. The postgres and superusers do get access
to the tables.
> What does \dn+ show?
=> \dn+
List of schemas
Name | Owner | Access privileges | Description
+--+--+
public | postgres | postgres=UC/postgres+| standard public schema
| | pgsql=UC/postgres|
And after restoring with "pg_restore -C -c -d postgres", when it works
correctly again, then it shows:
-> \dn+
List of schemas
Name | Owner | Access privileges | Description
+--+--+
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres+|
| | pgsql=UC/postgres|
So that was the command I was searching for. Thank You!
For now I hold on the bug...
cheers,
PMc
Re: Locked out of schema public (pg_dump lacks backup of the grant)
Hello Tom, thank You very much. > We improved that situation in v11, I believe. What I see for this > case these days is per commit 5955d9341: > [...] Ah, well. I don't fully understand that, but as the iessue appears to be known, then that is fine with me. This thing is just bad if one never seriously worked with schemas and has no immediate idea what could have hit - especially when that happens on the top of a stack of open windows with other issues. :( cheers, PMc
