pg_dump: VACUUM and REINDEXING

[Hasan Marzooq]

Hello!

I've some questions around Backup & Restore.

1: Is it necessary to perform a VACUUM and REINDEXING operation after
restoring the dump from Postgres 9.6 to Postgres 13? The dump size could be
1/2 TB to 1 TB.

2: Also, are there any other operations that are recommended to perform
after pg_restore?

3: What is the minimum required disk space if taking a dump on the same
machine where the source database exists? Is it the "size of the current
data folder x 2"?

Thanks.
Hasan

Re: pg_dump: VACUUM and REINDEXING

[Hasan Marzooq]

Hello!

Thanks Guillaume and Ron!

I understand REINDEXING is not required, and as Guillaume highlighted,
vacuum will still be needed after pg_restore.

Is it ok to perform a "standard" vacuum or do we need a "FULL" vacuum after
pg_restore?

Also, I think finding tables which have dead rows and then performing
vacuum on those tables only to save some time/processing here.

@Ron: Yes, we're using --jobs=`nproc` and it has significantly improved the
pg_dump/pg_restore processes. I see there is a similar option "parallel'
with VACUUM as well.

Thanks!

Hasan



On Sat, 7 May 2022 at 18:07, Guillaume Lelarge 
wrote:

> Le sam. 7 mai 2022 à 10:21, Ron  a écrit :
>
>> On 5/6/22 21:35, Hasan Marzooq wrote:
>>
>> Hello!
>>
>> I've some questions around Backup & Restore.
>>
>> 1: Is it necessary to perform a VACUUM and REINDEXING operation after
>> restoring the dump from Postgres 9.6 to Postgres 13? The dump size could be
>> 1/2 TB to 1 TB.
>>
>>
>> Perform VACUUM after there have been many updates and deletes.  There
>> have been zero updates and deleted after pg_restore; therefore, *no need
>> to vacuum*.
>>
>>
> I disagree. You're right about the "zero updates and deletes", so no need
> to vacuum for bloat. But you need vacuum to get the visibility map of each
> relation, so that the planner can use index-only scans.
>
>
> --
> Guillaume.
>

pg_config header files are missing - Postgres 13 - Amazon Linux 2

[Hasan Marzooq]

Hello!

We've a web application in play framework which uses Postgres 9.6 with plv8
and uuid-ossp extension. We now want to use Postgres 13 instead of 9.6. To
achieve this, I'm trying to install Postgres 13 (on the same host where 9.6
is installed) with plv8 and uuid-ossp extension.

PLV8  "make" uses "pg_config" to get the latest
version of Postgres (in case multiple Postgres are installed). However,
when running "make" with Postgres 13's pg_config, its giving error:

cat: /usr/pgsql-13/include/server/pg_config*.h: No such file or directory

using command: make PG_CONFIG=/usr/pgsql-13/bin/pg_config

I see the header files are missing, in fact there is no "include" folder
inside "/usr/pgsql-13/"
--
# ll /usr/pgsql-13
total 16
drwxr-xr-x 2 root root 4096 Dec 29 02:31 bin
drwxr-xr-x 3 root root   23 Dec 29 02:31 doc
drwxr-xr-x 3 root root 4096 Dec 29 02:31 lib
drwxr-xr-x 8 root root 4096 Dec 29 02:31 share
---

Header files (and include folder) do exist in Postgres 9.6's pg_config.

After a bit of research I found that probably installing
"postgresql13-devel.x86_64" will install pg_config with headers. However,
when installing "postgresql13-devel.x86_64" via yum, it says,
--
Error: Package: postgresql13-devel-13.5-1PGDG.rhel7.x86_64 (pgdg13)
   Requires: llvm-toolset-7-clang >= 4.0.1
--
I didn't find "llvm-toolset-7-clang" from yum repo, except from the RHEL
subscription manager, which is paid for non-dev stuff. So I tried to build
llvm-toolset-clang from scratch, but that doesn't solve the problem.

I'm not sure why the header files of Postgres 13's pg_config are missing?
It appears pg_config got installed as part of Postgres 13 itself. I'm also
not sure if copying 9.6 pg_config header files to 13 is the correct way?

What would be the next steps here? Please advise.

Some info about host machine: This is Amazon Linux 2
---
# cat /proc/version
Linux version 4.14.256-197.484.amzn2.x86_64 (mockbuild@ip-10-0-37-156) (gcc
version 7.3.1 20180712 (Red Hat 7.3.1-13) (GCC)) #1 SMP Tue Nov 30 00:17:50
UTC 2021
---
--
yum list installed | grep -i postgre
postgresql13.x86_6413.5-1PGDG.rhel7  @pgdg13
postgresql13-contrib.x86_6413.5-1PGDG.rhel7  @pgdg13
postgresql13-libs.x86_64   13.5-1PGDG.rhel7  @pgdg13
postgresql13-server.x86_64 13.5-1PGDG.rhel7  @pgdg13
postgresql96.x86_649.6.24-1PGDG.rhel6@pgdg96
postgresql96-contrib.x86_649.6.24-1PGDG.rhel6@pgdg96
postgresql96-devel.x86_64  9.6.24-1PGDG.rhel6@pgdg96
postgresql96-libs.x86_64   9.6.24-1PGDG.rhel6@pgdg96
postgresql96-server.x86_64 9.6.24-1PGDG.rhel6@pgdg96
---

Thanks!
Naqvi

Re: pg_config header files are missing - Postgres 13 - Amazon Linux 2

[Hasan Marzooq]

Hi.
I tried "yum install centos-release-scl-rh" before but it gives the below
error.
--
# yum install centos-release-scl-rh
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
213 packages excluded due to repository priority protections
No package centos-release-scl-rh available.
Error: Nothing to do


Postgres 9.6 on this machine (Amazon Linux 2) is installed via PGDG repo,
so I tried installing Postgres 13 as well, and I can see it has installed
but only one more package I need that is "postgresql13-devel.x86_64" which
needs another dependency " llvm-toolset-7-clang >= 4.0.1" that is not
available from the same repo.

---
yum list installed | grep -i postgres
postgresql13.x86_6413.5-1PGDG.rhel7  @pgdg13
postgresql13-contrib.x86_6413.5-1PGDG.rhel7  @pgdg13
postgresql13-libs.x86_64   13.5-1PGDG.rhel7  @pgdg13
postgresql13-server.x86_64 13.5-1PGDG.rhel7  @pgdg13
postgresql96.x86_649.6.24-1PGDG.rhel6@pgdg96
postgresql96-contrib.x86_649.6.24-1PGDG.rhel6@pgdg96
postgresql96-devel.x86_64  9.6.24-1PGDG.rhel6@pgdg96
postgresql96-libs.x86_64   9.6.24-1PGDG.rhel6@pgdg96
postgresql96-server.x86_64 9.6.24-1PGDG.rhel6@pgdg96
---


On Thu, 30 Dec 2021 at 17:48, Magnus Hagander  wrote:

> On Thu, Dec 30, 2021 at 8:10 AM Hasan Marzooq 
> wrote:
> >
> > Hello!
> >
> > We've a web application in play framework which uses Postgres 9.6 with
> plv8 and uuid-ossp extension. We now want to use Postgres 13 instead of
> 9.6. To achieve this, I'm trying to install Postgres 13 (on the same host
> where 9.6 is installed) with plv8 and uuid-ossp extension.
> >
> > PLV8 "make" uses "pg_config" to get the latest version of Postgres (in
> case multiple Postgres are installed). However, when running "make" with
> Postgres 13's pg_config, its giving error:
> >
> > cat: /usr/pgsql-13/include/server/pg_config*.h: No such file or directory
> >
> > using command: make PG_CONFIG=/usr/pgsql-13/bin/pg_config
> >
> > I see the header files are missing, in fact there is no "include" folder
> inside "/usr/pgsql-13/"
> > --
> > # ll /usr/pgsql-13
> > total 16
> > drwxr-xr-x 2 root root 4096 Dec 29 02:31 bin
> > drwxr-xr-x 3 root root   23 Dec 29 02:31 doc
> > drwxr-xr-x 3 root root 4096 Dec 29 02:31 lib
> > drwxr-xr-x 8 root root 4096 Dec 29 02:31 share
> > ---
> >
> > Header files (and include folder) do exist in Postgres 9.6's pg_config.
> >
> > After a bit of research I found that probably installing
> "postgresql13-devel.x86_64" will install pg_config with headers. However,
> when installing "postgresql13-devel.x86_64" via yum, it says,
>
> > --
> > Error: Package: postgresql13-devel-13.5-1PGDG.rhel7.x86_64 (pgdg13)
> >Requires: llvm-toolset-7-clang >= 4.0.1
> > --
> > I didn't find "llvm-toolset-7-clang" from yum repo, except from the RHEL
> subscription manager, which is paid for non-dev stuff. So I tried to build
> llvm-toolset-clang from scratch, but that doesn't solve the problem.
>
> Note that the PGDG repositories are not supported on Amazon Linux.
> https://www.postgresql.org/download/linux/redhat/ has a list of
> supported platforms for them.
>
> On CentOS, which is, you need to run:
> yum install centos-release-scl-rh
>
> To get access to the required packages. I have no idea if the same
> thing exists on Amazon Linux, but it might be worth a try.
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/
>  Work: https://www.redpill-linpro.com/
>

Re: pg_config header files are missing - Postgres 13 - Amazon Linux 2

[Hasan Marzooq]

This problem posted in OP is resolved. I did some workaround. I copied the
required "pg_config*.h" from another machine where PG13 was installed via
amazon linux 2 repos.

> I don't believe 9.6 was supported either, but it may have worked "by
chance".

I'm not sure whether it worked "by chance" or otherwise, because it looks
like Amazon Linux 2 is based on "RHEL 7" as per below:

Note the "ID_LIKE":

# cat /etc/*elease
NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/";
Amazon Linux release 2 (Karoo)


And:
---
# rpm -E %{rhel}
7


Thanks!


On Sun, 2 Jan 2022 at 00:58, Magnus Hagander  wrote:

> On Fri, Dec 31, 2021 at 3:32 AM Hasan Marzooq 
> wrote:
> >
> > Hi.
> > I tried "yum install centos-release-scl-rh" before but it gives the
> below error.
> > --
> > # yum install centos-release-scl-rh
> > Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
> > 213 packages excluded due to repository priority protections
> > No package centos-release-scl-rh available.
> > Error: Nothing to do
> > 
> >
> > Postgres 9.6 on this machine (Amazon Linux 2) is installed via PGDG
> repo, so I tried installing Postgres 13 as well, and I can see it has
> installed but only one more package I need that is
> "postgresql13-devel.x86_64" which needs another dependency "
> llvm-toolset-7-clang >= 4.0.1" that is not available from the same repo.
>
> I don't believe 9.6 was supported either, but it may have worked "by
> chance".
>
> The llvm dependency comes from the JIT functionality, which was added
> in PostgreSQL 11, so it not being a dependency in an older version
> than that makes perfect sense.
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/
>  Work: https://www.redpill-linpro.com/
>