Re: perl path issue

[Prakash Ramakrishnan]

Hi Adrian,

Yes am using edb standard edition and installed pgbackrest in prod and dev.

nothing different am trying to restore the backup prod to dev cluster .

  * Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for
module DBD::Pg: libpq.so.5: cannot open shared object file: No such file or
directory at /usr/lib64/perl5/DynaLoader.pm line 190.*
at /loader/0x1ec1528/pgBackRest/Db.pm line 10.
at /loader/0x1ec1528/pgBackRest/Main.pm line 12.
pgBackRest::Main::__ANON__('Can\'t load
\'/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so\...') called at
/usr/share/perl5/vendor_perl/Carp.pm line 100

May be above path is corrupt or something issue not able to load the path.

Regards,
Prakash.R


On Sat, May 11, 2019 at 7:18 PM Adrian Klaver 
wrote:

> On 5/10/19 6:49 PM, Prakash Ramakrishnan wrote:
> > Hi Adrian,
>
> If I am following the below correctly you have EDB Postgres and
> pgBackRest running together on the prod server, correct?
>
> If so what was done different/is different on the dev server?
>
> >
> > 1) How was the production Postgres installed(EDB, native packages) and
> > on what OS?
> >
> > prod has been installed edb standard edition .run file and os CENTOS 7.5
> >
> > 2) The dev Postgres is the one installed using EDB installer, correct?
> >
> > yes
> >
> > 3) Define working fine. In other words where you/are you using a backup
> > method other then pgBackRest on the production and dev server?
> >
> > Please find prod config file and backup details,
> >
> > ==> cat /etc/pgbackrest.conf
> > [global]
> > repo1-path=/pgBACKUP/A4_sydcosafpp001
> > retention-diff=3
> > retention-full=1
> > retention-archive=2
> > start-fast=y
> > process-max=12
> > archive-async=y
> >
> >
> > [global:archive-push]
> > process-max=4
> >
> > [A4_sydcosafpp001]
> > pg1-path=/Postgres/pgDATA/data
> > postg...@sydcosafpp001.enterprisenet.org:/home/postgres
> > ==> cd /pgBACKUP/A4_sydcosafpp001/
> > archive/ backup/
> > postg...@sydcosafpp001.enterprisenet.org:/home/postgres
> > ==> cd /pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001/
> > postg...@sydcosafpp001.enterprisenet.org:
> /pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001
> > ==> ls -lrth
> > total 114K
> > drwxr-x---. 4 postgres postgres   44 Jan  7 01:08 backup.history
> > drwxr-x---. 3 postgres postgres   96 May  4 21:30 20190504-190001F
> > drwxr-x---. 3 postgres postgres   96 May  5 19:41
> > 20190504-190001F_20190505-190002I
> > drwxr-x---. 3 postgres postgres   96 May  6 20:28
> > 20190504-190001F_20190506-190002I
> > drwxr-x---. 3 postgres postgres   96 May  7 20:26
> > 20190504-190001F_20190507-190004I
> > drwxr-x---. 3 postgres postgres   96 May  8 20:28
> > 20190504-190001F_20190508-190002I
> > drwxr-x---. 3 postgres postgres   96 May  9 20:32
> > 20190504-190001F_20190509-190001I
> > drwxr-x---. 3 postgres postgres   96 May 10 20:32
> > 20190504-190001F_20190510-190001I
> > lrwxrwxrwx. 1 postgres postgres   33 May 10 20:32 latest ->
> > 20190504-190001F_20190510-190001I
> > -rw-r-. 1 postgres postgres 5.7K May 10 20:32 backup.info
> > 
> > -rw-r-. 1 postgres postgres 5.7K May 10 20:32 backup.info.copy
> >
> >
> >
> > 4) When you say single database are you talking about a particular
> > database in the Postgres cluster or the entire cluster?
> >
> > we need restore for single database for single cluster.
> >
> > 5) Why is pgBackRest essential to this? In other words why not use the
> > Postgres pg_dump/pg_restore tools?
> >
> > pgbackrest tool need for backup and restore larger databases.
> > if suppose am using pg_dump and restore they wont accept it and taking
> > too much time using parallel option also so we use for pgbackrest.
> >
> >
> > On Fri, May 10, 2019 at 9:44 PM Adrian Klaver  > > wrote:
> >
> > On 5/10/19 7:50 AM, Prakash Ramakrishnan wrote:
> >  > Hi Adrian,
> >  >
> >  > Production backup working fine now and previously dev server also
> >  > working fine.
> >  >
> >  > Now the business team wants to restore the single database using
> >  > pgbackrest tool so we have setup the pgbackrest.conf file is like
> to
> >  > point prod .
> >
> >
> > The above is not much to go on. So a list of questions below. Please
> > answer them all, thanks:
> >
> > 1) How was the production Postgres installed(EDB, native packages)
> and
> > on what OS?
> >
> > 2) The dev Postgres is the one installed using EDB installer,
> correct?
> >
> > 3) Define working fine. In other words where you/are you using a
> backup
> > method other then pgBackRest on the production and dev server?
> >
> > 4) When you say single database are you talking about a particular
> > database in the Postgres cluster or the entire cluster?
> >
> > 5) Why is pgBackRest essential to this? In other words why not use
> the
> > Postgres pg_dump/pg_restore tools?
> >
> >
> > As to below, this more then a Perl i

Re: perl path issue

[Ravi Krishna]

what does ls /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so return?

If there is no such file, then it means that on the dev server perl DBD was 
installed differently
than prod. I am assuming LD_LIBRARY_PATH is also correctly set.

apologize if my advise is wrong. It has been eons since I worked with perl DBD.

Re: perl path issue

[Achilleas Mantzios]

On 13/5/19 12:20 μ.μ., Prakash Ramakrishnan wrote:

Hi Adrian,

Yes am using edb standard edition and installed pgbackrest in prod and dev.

nothing different am trying to restore the backup prod to dev cluster .

* Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for module 
DBD::Pg: libpq.so.5: cannot open shared object file: No such file or directory 
at /usr/lib64/perl5/DynaLoader.pm line 190.*
        at /loader/0x1ec1528/pgBackRest/Db.pm line 10.
        at /loader/0x1ec1528/pgBackRest/Main.pm line 12.
        pgBackRest::Main::__ANON__('Can\'t load 
\'/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so\...') called at 
/usr/share/perl5/vendor_perl/Carp.pm line 100


How did you install libdbd-pg-perl ?
There is a dependency from libdbd-pg-perl to libpq5 .



May be above path is corrupt or something issue not able to load the path.

Regards,
Prakash.R


On Sat, May 11, 2019 at 7:18 PM Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 5/10/19 6:49 PM, Prakash Ramakrishnan wrote:
> Hi Adrian,

If I am following the below correctly you have EDB Postgres and
pgBackRest running together on the prod server, correct?

If so what was done different/is different on the dev server?

>
> 1) How was the production Postgres installed(EDB, native packages) and
> on what OS?
>
> prod has been installed edb standard edition .run file and os CENTOS 7.5
>
> 2) The dev Postgres is the one installed using EDB installer, correct?
>
> yes
>
> 3) Define working fine. In other words where you/are you using a backup
> method other then pgBackRest on the production and dev server?
>
> Please find prod config file and backup details,
>
> ==> cat /etc/pgbackrest.conf
> [global]
> repo1-path=/pgBACKUP/A4_sydcosafpp001
> retention-diff=3
> retention-full=1
> retention-archive=2
> start-fast=y
> process-max=12
> archive-async=y
>
>
> [global:archive-push]
> process-max=4
>
> [A4_sydcosafpp001]
> pg1-path=/Postgres/pgDATA/data
> postg...@sydcosafpp001.enterprisenet.org:/home/postgres
> ==> cd /pgBACKUP/A4_sydcosafpp001/
> archive/ backup/
> postg...@sydcosafpp001.enterprisenet.org:/home/postgres
> ==> cd /pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001/
> 
postg...@sydcosafpp001.enterprisenet.org:/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001
> ==> ls -lrth
> total 114K
> drwxr-x---. 4 postgres postgres   44 Jan  7 01:08 backup.history
> drwxr-x---. 3 postgres postgres   96 May  4 21:30 20190504-190001F
> drwxr-x---. 3 postgres postgres   96 May  5 19:41
> 20190504-190001F_20190505-190002I
> drwxr-x---. 3 postgres postgres   96 May  6 20:28
> 20190504-190001F_20190506-190002I
> drwxr-x---. 3 postgres postgres   96 May  7 20:26
> 20190504-190001F_20190507-190004I
> drwxr-x---. 3 postgres postgres   96 May  8 20:28
> 20190504-190001F_20190508-190002I
> drwxr-x---. 3 postgres postgres   96 May  9 20:32
> 20190504-190001F_20190509-190001I
> drwxr-x---. 3 postgres postgres   96 May 10 20:32
> 20190504-190001F_20190510-190001I
> lrwxrwxrwx. 1 postgres postgres   33 May 10 20:32 latest ->
> 20190504-190001F_20190510-190001I
> -rw-r-. 1 postgres postgres 5.7K May 10 20:32 backup.info 

> 
> -rw-r-. 1 postgres postgres 5.7K May 10 20:32 backup.info.copy
>
>
>
> 4) When you say single database are you talking about a particular
> database in the Postgres cluster or the entire cluster?
>
> we need restore for single database for single cluster.
>
> 5) Why is pgBackRest essential to this? In other words why not use the
> Postgres pg_dump/pg_restore tools?
>
> pgbackrest tool need for backup and restore larger databases.
> if suppose am using pg_dump and restore they wont accept it and taking
> too much time using parallel option also so we use for pgbackrest.
>
>
> On Fri, May 10, 2019 at 9:44 PM Adrian Klaver mailto:adrian.kla...@aklaver.com>
> >> 
wrote:
>
>     On 5/10/19 7:50 AM, Prakash Ramakrishnan wrote:
>      > Hi Adrian,
>      >
>      > Production backup working fine now and previously dev server also
>      > working fine.
>      >
>      > Now the business team wants to restore the single database using
>      > pgbackrest tool so we have setup the pgbackrest.conf file is like 
to
>      > point prod .
>
>
>     The above is not much to go on. So a list of questions below. Please
>     answer them all, thanks:
>
>     1) How was the production Postgres installed(EDB, native packages) and
>     on what OS?
>
>     2) The dev Postgres is the one installed using EDB installer, correct?
>
>     3

Re: perl path issue

[Prakash Ramakrishnan]

Hi Ravi,

Path is there ,

==>  ls /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
postg...@sydcosausd001.enterprisenet.org:/home/postgres

and perl DBD also installed but it cant load that is the issue please
advise me.


Regards,
Prakash.R

On Mon, May 13, 2019 at 3:06 PM Ravi Krishna  wrote:

> what does ls /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so return?
>
> If there is no such file, then it means that on the dev server perl DBD
> was installed differently
> than prod. I am assuming LD_LIBRARY_PATH is also correctly set.
>
> apologize if my advise is wrong. It has been eons since I worked with perl
> DBD.
>
>

-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: perl path issue

[Ravi Krishna]

> and perl DBD also installed but it cant load that is the issue please advise 
> me.

what is the output of LD_LIBRARY_PATH on both dev and prod

Re: perl path issue

[Prakash Ramakrishnan]

Hi Ravi,

Please find the below details,

Dev:

==> echo $LD_LIBRARY_PATH
/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE:/opt/rh/rh-perl526/root/usr/lib64:/usr/lib64/perl5/CORE:/opt/edb/languagepack-10/Perl-5.24/lib/CORE
postg...@sydcosausd001.enterprisenet.org:/home/postgres


Prod:
==

==> echo $LD_LIBRARY_PATH
/usr/lib64/perl5/CORE:/opt/app/PostgreSQL/10/lib:/opt/edb/languagepack-10/Perl-5.24/lib/CORE:/opt/app/PostgreSQL/10/lib
postg...@sydcosafpp001.enterprisenet.org:/home/postgres




On Mon, May 13, 2019 at 3:20 PM Ravi Krishna  wrote:

> > and perl DBD also installed but it cant load that is the issue please
> advise me.
>
> what is the output of LD_LIBRARY_PATH on both dev and prod
>


-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: logical replication initiate via manual pg_dump

[Vijaykumar Jain]

update:
i dropped all the indexes on the dest db tables, but the primary key
and unique constraints from base tables in logical replication to
ensure replica identity.

and retriggered the logical replication from scratch.
it completed in 4 hours. (which otherwise ran for 4 days)

so i guess it was the "too many indexes" slowed down copy way too much.
anyways, i got to explore pg_replication_origin_advance which was a cool thing.


Regards,
Vijay

On Fri, May 10, 2019 at 8:59 PM Vijaykumar Jain  wrote:
>
> Hey Guys,
>
> tl;dr, but incase i missed something, i can follow up on this with more 
> details.
>
>
> I have a setup where i try to upgrade a cluster from pg10 to pg11 via
> logical replication with minimum downtime.
> its a database that is 500GB with 1 table having 350GB of data (+
> bloat) and 100GB of indexes.
>
> now when i triggered logical replication, it took more than 2 days
> (and still around 10% remaining) to catch up on the data. the
> publisher shows copy table to stdout still running.
> i am not sure of the internals, but given a case that in a test prep
> with no active connections and DMLs it took around 2 days, does seem
> like i am missing something.
>
> on both pg10 and pg11
> ram 32GB
> cpu 8
> SSD
> max_wal_size 100GB
> checkpoint_timeout 30min
> shared_buffers 8GB
>
> on pg10
> (copy still running on the huge table)
> select (now() - query_start)::interval, query from pg_stat_activity;
> 2 days 19:03:12.799767 | COPY public. TO STDOUT
>
> this is how disk looks like on pg10 (publisher)
> du -sh /var/lib/postgresql/1*/main/{pg_wal*,base*}
> 78G /var/lib/postgresql/10/main/pg_wal
> 467G /var/lib/postgresql/10/main/base
>
>
> on pg11
> du -sh /var/lib/postgresql/1*/main/{pg_wal*,base*}
> 65G /var/lib/postgresql/11/main/pg_wal
> 417G /var/lib/postgresql/11/main/base
>
> now although this is kind of upgrade involves very little downtime,
> but it required around 2x the disk on the original server while the
> replication was running, but there was not way to correctly estimate
> the disk required for logical replication to finish and move over to
> pg11.
>
> -
>
> now
> given the above problem,
> i tried to read thru with a goal of if i can do an initial sync via
> pg_dump and start the restore from there.
>
> https://www.slideshare.net/noriyoshishinoda/pgconfasia-2017-logical-replication-internals-english
> https://medium.com/avitotech/recovery-use-cases-for-logical-replication-in-postgresql-10-a1e6bab03072
>
> and setup a small test cluster as to test if i can pg_dump and
> pg_restore and then start replication from the restart_lsn of the
> primary/publisher.
>
> ***
> demo lab
> (all pg11 for now)
> pg1 (primary/publisher on port 3000)
> pg2  (hot_standby replica on port 3001)
> pg3  (subscriber on port 3002)
>
> ***
> on pg1 (create some tables and trigger to ensure trigger does not fire
> on subscriber)
> example=# CREATE TABLE public.company (
> example(# id integer NOT NULL,
> example(# name text NOT NULL,
> example(# age integer NOT NULL,
> example(# address character(50),
> example(# salary real
> example(# );
> CREATE TABLE
> example=# ALTER TABLE ONLY public.company
> example-# ADD CONSTRAINT company_pkey PRIMARY KEY (id);
> ALTER TABLE
> example=# CREATE TABLE public.audit (
> example(# emp_id integer NOT NULL,
> example(# entry_date text NOT NULL
> example(# );
> CREATE TABLE
> example=# ALTER TABLE ONLY public.audit
> example-# ADD CONSTRAINT audit_pkey PRIMARY KEY (emp_id);
> ALTER TABLE
> example=# CREATE FUNCTION public.auditlogfunc() RETURNS trigger
> example-# LANGUAGE plpgsql
> example-# AS $$
> example$#BEGIN
> example$#   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID,
> current_timestamp);
> example$#   RETURN NEW;
> example$#END;
> example$# $$;
> CREATE FUNCTION
> example=# CREATE TRIGGER example_trigger AFTER INSERT ON
> public.company FOR EACH ROW EXECUTE PROCEDURE public.auditlogfunc();
>
>
> ***
> setup pg2 as hot_standby replica
> and dump the schema of pg1 example database on pg3.
>
> ***
> on pg1 (insert some dummy data)
> insert into company select x, x::text, x, 'address-' || x::text, x
> from generate_series(1, 1500) x;
>
> ***
> on pg1
> create publication pg1 for all tables;
>
> ***
> on pg2
> verify replica is up and running and data replicated (fine)
> example=# select count(1) from company;
>  count
> ---
>   1500
> (1 row)
>
>
> ***
> on pg3
> *take a dump and restore the dump on pg3.
>
> pg_dump -p 3000 -U postgres -Fc --serializable-deferrable
> --no-subscriptions --no-publications -d example | pg_restore -p 3002
> -U postgres -C -d example
>
> *then create subscriptions to pg1
>
> create subscription pg3 connection 'dbname=example port=3000
> user=postgres' publication pg1 with (enabled = false, copy_data =
> false);
>
>
> ***
> on pg1
> *get the last restart_lsn value from pg_replication_slots
>
> select * from pg_replication_slots;
>  slot_name | plugi

Re: perl path issue

[Achilleas Mantzios]

On 13/5/19 12:54 μ.μ., Prakash Ramakrishnan wrote:

Hi Ravi,

Please find the below details,


what does :
# ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
say?



Dev:

==> echo $LD_LIBRARY_PATH
/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE:/opt/rh/rh-perl526/root/usr/lib64:/usr/lib64/perl5/CORE:/opt/edb/languagepack-10/Perl-5.24/lib/CORE
postg...@sydcosausd001.enterprisenet.org:/home/postgres


Prod:
==

==> echo $LD_LIBRARY_PATH
/usr/lib64/perl5/CORE:/opt/app/PostgreSQL/10/lib:/opt/edb/languagepack-10/Perl-5.24/lib/CORE:/opt/app/PostgreSQL/10/lib
postg...@sydcosafpp001.enterprisenet.org:/home/postgres




On Mon, May 13, 2019 at 3:20 PM Ravi Krishna mailto:ravi_kris...@aol.com>> wrote:

> and perl DBD also installed but it cant load that is the issue please 
advise me.

what is the output of LD_LIBRARY_PATH on both dev and prod



--



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call : 
+91-8939599426



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

terminating walsender process due to replication timeout

[AYahorau]

Hello PostgreSQL Community!

I faced an issue on my linux machine using Postgres 11.3 .
I have 2 nodes in db cluster: master and standby.
I tried to perform a plenty of long-running  queries which lead to the 
databases desynchronization:
terminating walsender process due to replication timeout

Here is the output in debug mode:
2019-05-13 13:21:33 FET 0 DEBUG:  sending replication keepalive
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  StartTransaction(1) name: unnamed; 
blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 DEBUG:  CommitTransaction(1) name: unnamed; 
blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
2019-05-13 13:21:34 FET 0 LOG:  terminating walsender process due to 
replication timeout


The issue is reproducible. I configure 2 nodes cluster, download 
demo_small.zip from https://edu.postgrespro.ru/ and run the following 
command:
psql -U user1 -f demo_small.sql db1
and I get the observed behaviour.


I know that I can increase wal_sender_timeout value to avoid this 
behaviour (currently wal_sender_timeout is equal to 1 second.)
To be honest I don't want to increase wal_sender_timeout because I would 
like to detect some network issues quickly.

After having googled I found that someone faced a similar issue 
https://www.postgresql.org/message-id/e082a56a-fd95-a250-3bae-0fff93832...@2ndquadrant.com
 
which was fixed in  PostgreSQL 9.4.16.


Is my issue the same as described here 
https://www.postgresql.org/message-id/e082a56a-fd95-a250-3bae-0fff93832...@2ndquadrant.com
 
?
Is there any  other chance to avoid it without increasing 
wal_sender_timeout?


Thank you in advance.
Regards, 
Andrei

Re: perl path issue

[Adrian Klaver]

On 5/13/19 2:20 AM, Prakash Ramakrishnan wrote:

Hi Adrian,

Yes am using edb standard edition and installed pgbackrest in prod and dev.


To be clear your have on the prod server:

1) Postgres installed via EDB installer.

2) You have pgBackRest.

3) You can take a backup using pgBackRest from the Postgres server.


If that is the case how did you install pgBackRest on the prod server?

In other words what is different between the prod and dev setups of 
Postgres and pgBackRest?




nothing different am trying to restore the backup prod to dev cluster .

* Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for module 
DBD::Pg: libpq.so.5: cannot open shared object file: No such file or 
directory at /usr/lib64/perl5/DynaLoader.pm line 190.*

         at /loader/0x1ec1528/pgBackRest/Db.pm line 10.
         at /loader/0x1ec1528/pgBackRest/Main.pm line 12.
         pgBackRest::Main::__ANON__('Can\'t load 
\'/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so\...') called at 
/usr/share/perl5/vendor_perl/Carp.pm line 100


May be above path is corrupt or something issue not able to load the path.

Regards,
Prakash.R




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: perl path issue

[Prakash Ramakrishnan]

Hi Adrian,

I did in pgbackrest installation below commands in prod server.

yum install
https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rp

m

yum install pgbackrest

and prod server backup working fine .

prod and dev file system mount points is different naming conversation.

so we have taken the restore directly using to prod .

dev:

postg...@sydcosausd001.enterprisenet.org:/home/postgres
==> df -h
Filesystem
Size  Used Avail Use% Mounted on
/dev/mapper/sysvg-root.vol
 20G   11G  8.9G  54% /
devtmpfs
 63G 0   63G   0% /dev
tmpfs
63G   20K   63G   1% /dev/shm
tmpfs
63G  154M   63G   1% /run
tmpfs
63G 0   63G   0% /sys/fs/cgroup
/dev/mapper/datavg-db.vol
 6.0T  4.7T  1.3T  79% /Postgres
/dev/mapper/sysvg-tmp.vol
 2.0G   95M  1.9G   5% /tmp
/dev/sda1
 973M  220M  753M  23% /boot
/dev/mapper/sysvg-home.vol
2.0G  594M  1.4G  31% /home
//sydwinappt001/A4_Import/
 1000G  395G  606G  40% /mnt/sydwinappt001_A4_Import
ausydisi01.enterprisenet.org:/ifs/auwatch/prd/auwatch/Adex/DataDump/A4
4.8T  3.8T  965G  81% /mnt/I_DataDump
tmpfs
13G 0   13G   0% /run/user/26
tmpfs
13G 0   13G   0% /run/user/0
tmpfs
13G 0   13G   0% /run/user/290789


prod:
===
postg...@sydcosafpp001.enterprisenet.org:/home/postgres
==> df -h
Filesystem
Size  Used Avail Use% Mounted on
/dev/mapper/rhel-root.vol
20G  9.6G  9.5G  51% /
devtmpfs
 32G 0   32G   0% /dev
tmpfs
32G   20K   32G   1% /dev/shm
tmpfs
32G  850M   31G   3% /run
tmpfs
32G 0   32G   0% /sys/fs/cgroup
/dev/sda1
 973M  187M  786M  20% /boot
/dev/mapper/rhel-tmp.vol
3.9G   34M  3.8G   1% /tmp
/dev/mapper/rhel-home.vol
 3.9G  115M  3.7G   3% /home
/dev/mapper/rhel-optapp.vol
 8.0G  537M  7.5G   7% /opt/app
/dev/mapper/datavg-postgres.vol
 5.0T  1.9T  3.2T  38% /Postgres
//sydwinappt001/A4_Import/
 1000G  395G  606G  40% /mnt/sydwinappt001_A4_Import
ausydisi01.enterprisenet.org:/ifs/auwatch/prd/auwatch/Adex/DataDump/A4
4.8T  3.8T  965G  81% /mnt/I_DataDump
ausydisi01.enterprisenet.org:/ifs/PostgresBackup
3.0T  723G  2.3T  24% /pgBACKUP
tmpfs
 6.3G 0  6.3G   0% /run/user/26
tmpfs
 6.3G 0  6.3G   0% /run/user/0
tmpfs
 6.3G 0  6.3G   0% /run/user/290789
postg...@sydcosafpp001.enterprisenet.org:/home/postgres

dev pgbackrest.conf file:
===

==> cat /etc/pgbackrest.conf
#[global]
#repo1-path=/Postgres/pgBACKUP/A4_sydcosausd001
#retention-diff=4
#retention-full=4
#retention-archive=2
#start-fast=y
#process-max=4
#archive-async=y


#[global:archive-push]
#process-max=4

#[A4_sydcosausd001]
#db-path=/Postgres/pgDATA/data


[global]
repo1-host=sydcosafpp001.enterprisenet.org
repo1-host-user=postgres
repo1-host-config=/etc/pgbackrest.conf
repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001

[A4_sydcosafpp001]
pg1-path=/Postgres/prakash_pgbackrest
postg...@sydcosausd001.enterprisenet.org:/home/postgres

restore command - pgbackrest --stanza=A4_sydcosafpp001
--log-level-console=info --db-path=/Postgres/prakash_pgbackrest --delta
--process-max=2 --db-include=A4_Copy restore
--repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001





On Mon, May 13, 2019 at 7:26 PM Adrian Klaver 
wrote:

> On 5/13/19 2:20 AM, Prakash Ramakrishnan wrote:
> > Hi Adrian,
> >
> > Yes am using edb standard edition and installed pgbackrest in prod and
> dev.
>
> To be clear your have on the prod server:
>
> 1) Postgres installed via EDB installer.
>
> 2) You have pgBackRest.
>
> 3) You can take a backup using pgBackRest from the Postgres server.
>
>
> If that is the case how did you install pgBackRest on the prod server?
>
> In other words what is different between the prod and dev setups of
> Postgres and pgBackRest?
>
> >
> > nothing different am trying to restore the backup prod to dev cluster .
> >
> > * Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for module
> > DBD::Pg: libpq.so.5: cannot open shared object file: No such file or
> > directory at /usr/lib64/perl5/DynaLoader.pm line 190.*
> >  at /loader/0x1ec1528/pgBackRest/Db.pm line 10.
> >  at /loader/0x1ec1528/pgBackRest/Main.pm line 12.
> >  pgBackRest::Main::__ANON__('Can\'t load
> > \'/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so\...') called at
> > /usr/share/perl5/vendor_perl/Carp.pm line 100
> >
> > May be above path is corrupt or something issue not able to load the
> path.
> >
> > Regards,
> > Prakash.R
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: perl path issue

[Adrian Klaver]

On 5/13/19 7:03 AM, Prakash Ramakrishnan wrote:

Hi Adrian,

I did in pgbackrest installation below commands in prod server.

yum install 
https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rp 
m



yum install pgbackrest


and prod server backup working fine .

prod and dev file system mount points is different naming conversation.

so we have taken the restore directly using to prod .


Hmm. I would follow Achilleas's suggestion and do:

ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: perl path issue

[Prakash Ramakrishnan]

Hi Adrian,

Please find the details,

==> ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
linux-vdso.so.1 =>  (0x7fffddd8f000)
libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5 (0x7f5ecdbd6000)
libm.so.6 => /lib64/libm.so.6 (0x7f5ecd8d4000)
libc.so.6 => /lib64/libc.so.6 (0x7f5ecd507000)
libssl.so.1.0.0 => /usr/lib64/perl5/CORE/libssl.so.1.0.0
(0x7f5ecd297000)
libcrypto.so.1.0.0 => /usr/lib64/perl5/CORE/libcrypto.so.1.0.0
(0x7f5ecce5d000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2
(0x7f5eccc1)
libldap_r-2.4.so.2 => /lib64/libldap_r-2.4.so.2 (0x7f5ecc9b1000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x7f5ecc795000)
/lib64/ld-linux-x86-64.so.2 (0x7f5ece056000)
libdl.so.2 => /lib64/libdl.so.2 (0x7f5ecc591000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x7f5ecc2a8000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x7f5ecc075000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x7f5ecbe71000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0
(0x7f5ecbc61000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x7f5ecba5d000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x7f5ecb844000)
liblber-2.4.so.2 => /lib64/liblber-2.4.so.2 (0x7f5ecb635000)
libsasl2.so.3 => /lib64/libsasl2.so.3 (0x7f5ecb418000)
libssl.so.10 => /lib64/libssl.so.10 (0x7f5ecb1a6000)
libcrypto.so.10 => /lib64/libcrypto.so.10 (0x7f5ecad45000)
libssl3.so => /lib64/libssl3.so (0x7f5ecaaf3000)
libsmime3.so => /lib64/libsmime3.so (0x7f5eca8cc000)
libnss3.so => /lib64/libnss3.so (0x7f5eca59f000)
libnssutil3.so => /lib64/libnssutil3.so (0x7f5eca37)
libplds4.so => /lib64/libplds4.so (0x7f5eca16c000)
libplc4.so => /lib64/libplc4.so (0x7f5ec9f67000)
libnspr4.so => /lib64/libnspr4.so (0x7f5ec9d29000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x7f5ec9b02000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7f5ec98cb000)
libz.so.1 => /lib64/libz.so.1 (0x7f5ec96b5000)
librt.so.1 => /lib64/librt.so.1 (0x7f5ec94ad000)
libpcre.so.1 => /lib64/libpcre.so.1 (0x7f5ec924b000)
libfreebl3.so => /lib64/libfreebl3.so (0x7f5ec9048000)
postg...@sydcosausd001.enterprisenet.org:/home/postgres


On Mon, May 13, 2019 at 8:04 PM Adrian Klaver 
wrote:

> On 5/13/19 7:03 AM, Prakash Ramakrishnan wrote:
> > Hi Adrian,
> >
> > I did in pgbackrest installation below commands in prod server.
> >
> > yum install
> >
> https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rp
> > <
> https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
> >m
> >
> >
> > yum install pgbackrest
> >
> >
> > and prod server backup working fine .
> >
> > prod and dev file system mount points is different naming conversation.
> >
> > so we have taken the restore directly using to prod .
>
> Hmm. I would follow Achilleas's suggestion and do:
>
> ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: perl path issue

[Ravi Krishna]

> 
> ==> ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
> linux-vdso.so.1 =>  (0x7fffddd8f000)
> libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5 (0x7f5ecdbd6000)


I may be wrong, but is the above path in the LD_LIBRARY_PATH. Why do you expect 
it to be picked auto magically.

Re: perl path issue

[Prakash Ramakrishnan]

Hi Ravi,

I tried manually export the lib path multiple times not working.

prod:
==> echo $LD_LIBRARY_PATH
/usr/lib64/perl5/CORE:/opt/app/PostgreSQL/10/lib:/opt/edb/languagepack-10/Perl-5.24/lib/CORE:/opt/app/PostgreSQL/10/lib

dev --- ==> echo $LD_LIBRARY_PATH

/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE:/opt/rh/rh-perl526/root/usr/lib64:/usr/lib64/perl5/CORE:/opt/edb/languagepack-10/Perl-5.24/lib/CORE
postg...@sydcosausd001.enterprisenet.org:/home/postgres

==> export
LD_LIBRARY_PATH=/usr/lib64/perl5/CORE:/opt/app/PostgreSQL/10/lib:/opt/edb/languagepack-10/Perl-5.24/lib/CORE:/opt/app/PostgreSQL/10/lib
postg...@sydcosausd001.enterprisenet.org:/home/postgres
==> echo $LD_LIBRARY_PATH

==> pgbackrest --stanza=A4_sydcosafpp001 --log-level-console=info
--db-path=/Postgres/prakash_pgbackrest --delta  --process-max=2
--db-include=A4_Copy restore
--repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001

   2019-05-13 19:55:05.954 P00   INFO: restore command begin 2.13:
--db-include=A4_Copy --delta --log-level-console=info
--pg1-path=/Postgres/prakash_pgbackrest --process-max=2 --repo1-host=
sydcosafpp001.enterprisenet.org --repo1-host-config=/etc/pgbackrest.conf
--repo1-host-user=postgres
--repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001
--stanza=A4_sydcosafpp001
Password:
ERROR [124]: : remote process on 'sydcosafpp001.enterprisenet.org'
terminated unexpectedly [124]: ERROR: [124]: process terminated due to an
unhandled exception:
*  Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for
module DBD::Pg: libpq.so.5: cannot open shared object file: No such file or
directory at /usr/lib64/perl5/DynaLoader.pm line 190.*
   at /loader/0xc54528/pgBackRest/Db.pm line 10.
   at /loader/0xc54528/pgBackRest/Main.pm line 12.
   pgBackRest::Main::__ANON__('Can\'t load
\'/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so\...') called at
/usr/share/perl5/vendor_perl/Carp.pm line 100


Regards,
Prakash.R

On Mon, May 13, 2019 at 8:39 PM Ravi Krishna  wrote:

> >
> > ==> ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
> > linux-vdso.so.1 =>  (0x7fffddd8f000)
> > libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5
> (0x7f5ecdbd6000)
> 
>
> I may be wrong, but is the above path in the LD_LIBRARY_PATH. Why do you
> expect it to be picked auto magically.
>
>
>

-- 



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On call
: +91-8939599426

Re: Upgrading locale issues

[Peter Eisentraut]

On 2019-05-03 15:35, Daniel Verite wrote:
> 'b' < 'a' is never true for any locale AFAIK,

But there is 'bb' < 'aa' in Danish. :-)

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: perl path issue

[Adrian Klaver]

On 5/13/19 8:14 AM, Prakash Ramakrishnan wrote:

Hi Ravi,

I tried manually export the lib path multiple times not working.



==> pgbackrest --stanza=A4_sydcosafpp001 --log-level-console=info 
--db-path=/Postgres/prakash_pgbackrest --delta  --process-max=2 
--db-include=A4_Copy restore 
--repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001 
 
            2019-05-13 19:55:05.954 P00   INFO: restore command begin 
2.13: --db-include=A4_Copy --delta --log-level-console=info 
--pg1-path=/Postgres/prakash_pgbackrest --process-max=2 
--repo1-host=sydcosafpp001.enterprisenet.org 
 
--repo1-host-config=/etc/pgbackrest.conf --repo1-host-user=postgres 
--repo1-path=/pgBACKUP/A4_sydcosafpp001/backup/A4_sydcosafpp001 
--stanza=A4_sydcosafpp001

Password:
ERROR [124]: : remote process on 'sydcosafpp001.enterprisenet.org 
' terminated unexpectedly [124]: 


Trying to figure out above:

sydcosausd001.enterprisenet.org is your dev

sydcosafpp001.enterprisenet.org  is your prod

Correct?

You are trying to run the pgBackRest on the dev machine, so why is there 
a remote process failure on the prod?




ERROR: [124]: process terminated due to an unhandled exception:
*  Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so' for 
module DBD::Pg: libpq.so.5: cannot open shared object file: No such file 
or directory at /usr/lib64/perl5/DynaLoader.pm line 190.*

        at /loader/0xc54528/pgBackRest/Db.pm line 10.
        at /loader/0xc54528/pgBackRest/Main.pm line 12.
        pgBackRest::Main::__ANON__('Can\'t load 
\'/usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so\...') called at 
/usr/share/perl5/vendor_perl/Carp.pm line 100



Regards,
Prakash.R

On Mon, May 13, 2019 at 8:39 PM Ravi Krishna > wrote:


 >
 > ==> ldd /usr/lib64/perl5/vendor_perl/auto/DBD/Pg/Pg.so
 >         linux-vdso.so.1 =>  (0x7fffddd8f000)
 >         libpq.so.5 => /usr/lib64/perl5/CORE/libpq.so.5
(0x7f5ecdbd6000)
                                 

I may be wrong, but is the above path in the LD_LIBRARY_PATH. Why do
you expect it to be picked auto magically.




--



Thanks,
Prakash.R
PostgreSQL - Offshore DBA support TCS / Nielsen Infrastructure Team On 
call : +91-8939599426



--
Adrian Klaver
adrian.kla...@aklaver.com

does postgresql backup require additional space on disk

[Julie Nishimura]

Hello,
we are almost out of disk space on one of our servers (99% full). If we run 
pg_dump to a diff location, does it require any additional disk space on our 
current server? I am asking, because on some other software, a backup might 
open transaction which keeps growing and eventually consume all space, keeping 
tran open for the backup duration. Please clarify? Thanks

Re: does postgresql backup require additional space on disk

[Adrian Klaver]

On 5/13/19 10:59 AM, Julie Nishimura wrote:

Hello,
we are almost out of disk space on one of our servers (99% full). If we 
run pg_dump to a diff location, does it require any additional disk 
space on our current server? I am asking, because on some other 
software, a backup might open transaction which keeps growing and 
eventually consume all space, keeping tran open for the backup duration. 
Please clarify? Thanks


A pg_dump is a point in time snapshot of the database, so if the cluster 
is running then it will advance past the dump snapshot. If the cluster 
is not active(close off connections to all but pg_dump) then pg_dump 
will be the only transaction.


I think the first thing to ask is what you are trying to achieve?

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Julie Nishimura]

We have added new disk volume and about to introduce new tablespace using this 
additional disk space. After that, I am going to alter all user dbs (and 
template db as well) by runnig the following command:

CREATE TABLESPACE vol4
OWNER postgres
LOCATION '/data/vol4';

ALTER DATABASE user_db_1
SET default_tablespace = 'vol4';

...for all dbs..

We have more than 70 different databases (the entire server is about 20 tb). 
However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not have valid 
backups, unfortunately. So, we were debating if we need to run backups first 
before introducing all these changes. But we have only 1% left (about 200 gb).

What would you suggest? How would you classify the risk of this operation 
(creating new tablespace and altering dbs to use it)?

Thanks,
Julie


From: Adrian Klaver 
Sent: Monday, May 13, 2019 11:19 AM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk

On 5/13/19 10:59 AM, Julie Nishimura wrote:
> Hello,
> we are almost out of disk space on one of our servers (99% full). If we
> run pg_dump to a diff location, does it require any additional disk
> space on our current server? I am asking, because on some other
> software, a backup might open transaction which keeps growing and
> eventually consume all space, keeping tran open for the backup duration.
> Please clarify? Thanks

A pg_dump is a point in time snapshot of the database, so if the cluster
is running then it will advance past the dump snapshot. If the cluster
is not active(close off connections to all but pg_dump) then pg_dump
will be the only transaction.

I think the first thing to ask is what you are trying to achieve?

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Migrating an application with Oracle temporary tables

[Michael Lewis]

*I did find a scenario where this approach does run into trouble.  That is,
if the function/procedure is executed against the permanent table and then
you go to run it against a temporary table.  In that case, I do get the
wrong answer, and I haven't yet figured out how to reset that without
dropping the procedure and re-defining it.  For my purposes, that is "good
enough" -- I can promise not to run such procedures against the temporary
table.*

You may want to be explicit and refer to the table with schema like *select
* from public.table_name *OR* select * from pg_temp.table_name *to avoid
confusion caused by the default behavior of using the temp table if it
exists.

Re: does postgresql backup require additional space on disk

[Adrian Klaver]

On 5/13/19 12:33 PM, Julie Nishimura wrote:
We have added new disk volume and about to introduce new tablespace 
using this additional disk space. After that, I am going to alter all 
user dbs (and template db as well) by runnig the following command:


Not sure why you need to move the template db's they sure not take much 
space?


More comments below.



CREATE TABLESPACE vol4
OWNER postgres
LOCATION '/data/vol4';

ALTER DATABASE user_db_1
SET default_tablespace = 'vol4';

...for all dbs..

We have more than 70 different databases (the entire server is about 20 
tb). However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not have 
valid backups, unfortunately. So, we were debating if we need to run 
backups first before introducing all these changes. But we have only 1% 
left (about 200 gb).


I have no experience with a setup of this size, someone else will have 
to provide real world advice. I would say taking a backup before 
preceding is a good idea. Pretty sure time is going to be as much an 
issue as space. This would apply to below also. The question is what 
options you have to bring cluster or databases down? In particular for 
below as:


https://www.postgresql.org/docs/11/sql-alterdatabase.html
"The fourth form changes the default tablespace of the database. Only 
the database owner or a superuser can do this; you must also have create 
privilege for the new tablespace. This command physically moves any 
tables or indexes in the database's old default tablespace to the new 
tablespace. The new default tablespace must be empty for this database, 
and no one can be connected to the database. Tables and indexes in 
non-default tablespaces are unaffected."




What would you suggest? How would you classify the risk of this 
operation (creating new tablespace and altering dbs to use it)?


Thanks,
Julie


*From:* Adrian Klaver 
*Sent:* Monday, May 13, 2019 11:19 AM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: does postgresql backup require additional space on disk
On 5/13/19 10:59 AM, Julie Nishimura wrote:

Hello,
we are almost out of disk space on one of our servers (99% full). If we 
run pg_dump to a diff location, does it require any additional disk 
space on our current server? I am asking, because on some other 
software, a backup might open transaction which keeps growing and 
eventually consume all space, keeping tran open for the backup duration. 
Please clarify? Thanks


A pg_dump is a point in time snapshot of the database, so if the cluster
is running then it will advance past the dump snapshot. If the cluster
is not active(close off connections to all but pg_dump) then pg_dump
will be the only transaction.

I think the first thing to ask is what you are trying to achieve?

--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Julie Nishimura]

Adrian, thanks for your reply. Couple of clarifications/questions:
1) we are on 8.3 for this server
2) I was under impression, that "ALTER DATABASE name SET TABLESPACE 
new_tablespace" won't move anything, and just utilize the new_tablespace for 
new tables/indexes. If we would want to move existing tables, we would need to 
move them one by one by
ALTER TABLE mytableschema.mytable SET TABLESPACE mynewtablespace

3) I thought if I want to have any newly created dbs go to the new vol, I need 
to alter template.
4) I was also thinking about changing parameters in config file to point to the 
newly volume and reload postgresql.conf


default_tablespace

temp_tablespaces


Am I wrong here?


Thanks,

Julie



From: Adrian Klaver 
Sent: Monday, May 13, 2019 1:10 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk

On 5/13/19 12:33 PM, Julie Nishimura wrote:
> We have added new disk volume and about to introduce new tablespace
> using this additional disk space. After that, I am going to alter all
> user dbs (and template db as well) by runnig the following command:

Not sure why you need to move the template db's they sure not take much
space?

More comments below.

>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server is about 20
> tb). However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not have
> valid backups, unfortunately. So, we were debating if we need to run
> backups first before introducing all these changes. But we have only 1%
> left (about 200 gb).

I have no experience with a setup of this size, someone else will have
to provide real world advice. I would say taking a backup before
preceding is a good idea. Pretty sure time is going to be as much an
issue as space. This would apply to below also. The question is what
options you have to bring cluster or databases down? In particular for
below as:

https://www.postgresql.org/docs/11/sql-alterdatabase.html
"The fourth form changes the default tablespace of the database. Only
the database owner or a superuser can do this; you must also have create
privilege for the new tablespace. This command physically moves any
tables or indexes in the database's old default tablespace to the new
tablespace. The new default tablespace must be empty for this database,
and no one can be connected to the database. Tables and indexes in
non-default tablespaces are unaffected."

>
> What would you suggest? How would you classify the risk of this
> operation (creating new tablespace and altering dbs to use it)?
>
> Thanks,
> Julie
>
> 
> *From:* Adrian Klaver 
> *Sent:* Monday, May 13, 2019 11:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 10:59 AM, Julie Nishimura wrote:
>> Hello,
>> we are almost out of disk space on one of our servers (99% full). If we
>> run pg_dump to a diff location, does it require any additional disk
>> space on our current server? I am asking, because on some other
>> software, a backup might open transaction which keeps growing and
>> eventually consume all space, keeping tran open for the backup duration.
>> Please clarify? Thanks
>
> A pg_dump is a point in time snapshot of the database, so if the cluster
> is running then it will advance past the dump snapshot. If the cluster
> is not active(close off connections to all but pg_dump) then pg_dump
> will be the only transaction.
>
> I think the first thing to ask is what you are trying to achieve?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Rob Sargent]

8 is very old but in 9.4 the doc says


The fourth form (your example) changes the default tablespace of the 
database. Only the database owner or a superuser can do this; you must 
also have create privilege for the new tablespace. This command 
*physically moves* any tables or indexes in the database's old default 
tablespace to the new tablespace. Note that tables and indexes in 
non-default tablespaces are not affected




On 5/13/19 2:26 PM, Julie Nishimura wrote:

Adrian, thanks for your reply. Couple of clarifications/questions:
1) we are on 8.3 for this server
2) I was under impression, that "ALTER DATABASE name SET TABLESPACE 
new_tablespace" won't move anything, and just utilize the 
new_tablespace for new tables/indexes. If we would want to move 
existing tables, we would need to move them one by one by

ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
/
/
3) I thought if I want to have any newly created dbs go to the new 
vol, I need to alter template.
4) I was also thinking about changing parameters in config file to 
point to the newly volume and reload postgresql.conf


default_tablespace

temp_tablespaces


Am I wrong here?


Thanks,

Julie




*From:* Adrian Klaver 
*Sent:* Monday, May 13, 2019 1:10 PM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: does postgresql backup require additional space on disk
On 5/13/19 12:33 PM, Julie Nishimura wrote:
> We have added new disk volume and about to introduce new tablespace
> using this additional disk space. After that, I am going to alter all
> user dbs (and template db as well) by runnig the following command:

Not sure why you need to move the template db's they sure not take much
space?

More comments below.

>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server is about 20
> tb). However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not 
have

> valid backups, unfortunately. So, we were debating if we need to run
> backups first before introducing all these changes. But we have only 1%
> left (about 200 gb).

I have no experience with a setup of this size, someone else will have
to provide real world advice. I would say taking a backup before
preceding is a good idea. Pretty sure time is going to be as much an
issue as space. This would apply to below also. The question is what
options you have to bring cluster or databases down? In particular for
below as:

https://www.postgresql.org/docs/11/sql-alterdatabase.html
"The fourth form changes the default tablespace of the database. Only
the database owner or a superuser can do this; you must also have create
privilege for the new tablespace. This command physically moves any
tables or indexes in the database's old default tablespace to the new
tablespace. The new default tablespace must be empty for this database,
and no one can be connected to the database. Tables and indexes in
non-default tablespaces are unaffected."

>
> What would you suggest? How would you classify the risk of this
> operation (creating new tablespace and altering dbs to use it)?
>
> Thanks,
> Julie
>
> 
> *From:* Adrian Klaver 
> *Sent:* Monday, May 13, 2019 11:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 10:59 AM, Julie Nishimura wrote:
>> Hello,
>> we are almost out of disk space on one of our servers (99% full). 
If we

>> run pg_dump to a diff location, does it require any additional disk
>> space on our current server? I am asking, because on some other
>> software, a backup might open transaction which keeps growing and
>> eventually consume all space, keeping tran open for the backup 
duration.

>> Please clarify? Thanks
>
> A pg_dump is a point in time snapshot of the database, so if the cluster
> is running then it will advance past the dump snapshot. If the cluster
> is not active(close off connections to all but pg_dump) then pg_dump
> will be the only transaction.
>
> I think the first thing to ask is what you are trying to achieve?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Karsten Hilbert]

On Mon, May 13, 2019 at 07:33:58PM +, Julie Nishimura wrote:

> We have added new disk volume and about to introduce new
> tablespace using this additional disk space. After that, I am
> going to alter all user dbs (and template db as well) by
> runnig the following command:
>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server
> is about 20 tb). However, for the largest dbs (16tb, 4 tb,
> and 3 tb), we do not have valid backups, unfortunately. So,
> we were debating if we need to run backups first before
> introducing all these changes. But we have only 1% left
> (about 200 gb).

You could move those databases first for which you do have
backups which should free up space in the old tablespace.
Then take backups of and move the remaining databases,
perhaps one by one.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: does postgresql backup require additional space on disk

[Julie Nishimura]

yeah, good idea. Thanks everybody


From: karsten.hilb...@gmx.net  on behalf of Karsten 
Hilbert 
Sent: Monday, May 13, 2019 2:12 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: does postgresql backup require additional space on disk

On Mon, May 13, 2019 at 07:33:58PM +, Julie Nishimura wrote:

> We have added new disk volume and about to introduce new
> tablespace using this additional disk space. After that, I am
> going to alter all user dbs (and template db as well) by
> runnig the following command:
>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server
> is about 20 tb). However, for the largest dbs (16tb, 4 tb,
> and 3 tb), we do not have valid backups, unfortunately. So,
> we were debating if we need to run backups first before
> introducing all these changes. But we have only 1% left
> (about 200 gb).

You could move those databases first for which you do have
backups which should free up space in the old tablespace.
Then take backups of and move the remaining databases,
perhaps one by one.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: does postgresql backup require additional space on disk

[Adrian Klaver]

On 5/13/19 1:26 PM, Julie Nishimura wrote:

Adrian, thanks for your reply. Couple of clarifications/questions:
1) we are on 8.3 for this server


8.3 does not have ALTER DATABASE name SET TABLESPACE :

https://www.postgresql.org/docs/8.3/sql-alterdatabase.html

It does appear until 8.4.


2) I was under impression, that "ALTER DATABASE name SET TABLESPACE 
new_tablespace" won't move anything, and just utilize the new_tablespace 
for new tables/indexes. If we would want to move existing tables, we 
would need to move them one by one by


No think of ALTER DATABASE name SET TABLESPACE  as a bulk operation of
ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/ 
across all existing tables.  This assumes the all existing tables live 
in the  current default tablespace. It is moot in your case as 8.3 is 
not capable of doing this.



ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
/
/


The above is your option in 8.3.

3) I thought if I want to have any newly created dbs go to the new vol, 
I need to alter template.


It is not required you can spec the tablespace in the CREATE DATABASE 
command:


https://www.postgresql.org/docs/8.3/sql-createdatabase.html

Though you can move them to make the tablespace the default. See above link.


4) I was also thinking about changing parameters in config file to point 
to the newly volume and reload postgresql.conf


default_tablespace

temp_tablespaces


You could that. If you do it, moving the template tables would be redundant.




Am I wrong here?


Thanks,

Julie





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Julie Nishimura]

Adrian, thanks for your reply. I previously found this link, which mentioned 
8.2, that is why I assumed it will work with 8.3.

Too bad we don't have dev with the same version. I guess I will create new 
tablespace using new vol, move the smallest db for which I have backup, create 
new test table and examine new/existing tables if they have moved by selecting 
tablespace from pg_tables... right?



http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html
Managing disk space using table spaces - Postgres OnLine Journal - Postgres 
OnLine Journal Magazine Jul 2017 - Dec 
2017
Below are steps to creating one. First create a folder on an available disk in 
your filesystem using an filesystem server administrative login; Next give full 
rights to the postgres server account (the one the daemon process runs under) 
or you can change the owner of the folder to the postgres account (in linux you 
can use chown postgres and on windows just use the administrative properties ...
www.postgresonline.com




From: Adrian Klaver 
Sent: Monday, May 13, 2019 2:47 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk

On 5/13/19 1:26 PM, Julie Nishimura wrote:
> Adrian, thanks for your reply. Couple of clarifications/questions:
> 1) we are on 8.3 for this server

8.3 does not have ALTER DATABASE name SET TABLESPACE :

https://www.postgresql.org/docs/8.3/sql-alterdatabase.html

It does appear until 8.4.


> 2) I was under impression, that "ALTER DATABASE name SET TABLESPACE
> new_tablespace" won't move anything, and just utilize the new_tablespace
> for new tables/indexes. If we would want to move existing tables, we
> would need to move them one by one by

No think of ALTER DATABASE name SET TABLESPACE  as a bulk operation of
ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
across all existing tables.  This assumes the all existing tables live
in the  current default tablespace. It is moot in your case as 8.3 is
not capable of doing this.

> ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
> /
> /

The above is your option in 8.3.

> 3) I thought if I want to have any newly created dbs go to the new vol,
> I need to alter template.

It is not required you can spec the tablespace in the CREATE DATABASE
command:

https://www.postgresql.org/docs/8.3/sql-createdatabase.html

Though you can move them to make the tablespace the default. See above link.


> 4) I was also thinking about changing parameters in config file to point
> to the newly volume and reload postgresql.conf
>
> default_tablespace
>
> temp_tablespaces

You could that. If you do it, moving the template tables would be redundant.

>
>
> Am I wrong here?
>
>
> Thanks,
>
> Julie
>
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Adrian Klaver]

On 5/13/19 3:27 PM, Julie Nishimura wrote:
Adrian, thanks for your reply. I previously found this link, which 
mentioned 8.2, that is why I assumed it will work with 8.3.


No, what the link showed is:

ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace

That is is the same as changing default_tablespace in postgresql.conf.

It is not the same as:

ALTER DATABASE name SET TABLESPACE



Too bad we don't have dev with the same version. I guess I will create 


You can use the same commands on dev to test, just not ALTER DATABASE 
name SET TABLESPACE as that is not in 8.3.


new tablespace using new vol, move the smallest db for which I have 


You cannot move a db as a unit, you will need to move the individual 
tables/indexes in the db.


backup, create new test table and examine new/existing tables if they 
have moved by selecting tablespace from pg_tables... right?


Assuming you either specify the new tablespace on table CREATE or have 
set default_tablespace to the new tablespace.





http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html
Managing disk space using table spaces - Postgres OnLine Journal - 
Postgres OnLine Journal Magazine Jul 2017 - Dec 2017 

Below are steps to creating one. First create a folder on an available 
disk in your filesystem using an filesystem server administrative login; 
Next give full rights to the postgres server account (the one the daemon 
process runs under) or you can change the owner of the folder to the 
postgres account (in linux you can use chown postgres and on windows 
just use the administrative properties ...

www.postgresonline.com




*From:* Adrian Klaver 
*Sent:* Monday, May 13, 2019 2:47 PM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: does postgresql backup require additional space on disk
On 5/13/19 1:26 PM, Julie Nishimura wrote:

Adrian, thanks for your reply. Couple of clarifications/questions:
1) we are on 8.3 for this server


8.3 does not have ALTER DATABASE name SET TABLESPACE :

https://www.postgresql.org/docs/8.3/sql-alterdatabase.html

It does appear until 8.4.


2) I was under impression, that "ALTER DATABASE name SET TABLESPACE 
new_tablespace" won't move anything, and just utilize the new_tablespace 
for new tables/indexes. If we would want to move existing tables, we 
would need to move them one by one by


No think of ALTER DATABASE name SET TABLESPACE  as a bulk operation of
ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
across all existing tables.  This assumes the all existing tables live
in the  current default tablespace. It is moot in your case as 8.3 is
not capable of doing this.


ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
/
/


The above is your option in 8.3.

3) I thought if I want to have any newly created dbs go to the new vol, 
I need to alter template.


It is not required you can spec the tablespace in the CREATE DATABASE
command:

https://www.postgresql.org/docs/8.3/sql-createdatabase.html

Though you can move them to make the tablespace the default. See above link.


4) I was also thinking about changing parameters in config file to point 
to the newly volume and reload postgresql.conf


default_tablespace

temp_tablespaces


You could that. If you do it, moving the template tables would be redundant.




Am I wrong here?


Thanks,

Julie





--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Julie Nishimura]

Adrian,
I think in my first emails in this chain about the effort i did specified it 
properly:

CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..

Then "default" part got lost in emails...:)



From: Adrian Klaver 
Sent: Monday, May 13, 2019 4:33 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk

On 5/13/19 3:27 PM, Julie Nishimura wrote:
> Adrian, thanks for your reply. I previously found this link, which
> mentioned 8.2, that is why I assumed it will work with 8.3.

No, what the link showed is:

ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace

That is is the same as changing default_tablespace in postgresql.conf.

It is not the same as:

ALTER DATABASE name SET TABLESPACE

>
> Too bad we don't have dev with the same version. I guess I will create

You can use the same commands on dev to test, just not ALTER DATABASE
name SET TABLESPACE as that is not in 8.3.

> new tablespace using new vol, move the smallest db for which I have

You cannot move a db as a unit, you will need to move the individual
tables/indexes in the db.

> backup, create new test table and examine new/existing tables if they
> have moved by selecting tablespace from pg_tables... right?

Assuming you either specify the new tablespace on table CREATE or have
set default_tablespace to the new tablespace.

>
>
> http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html
> Managing disk space using table spaces - Postgres OnLine Journal -
> Postgres OnLine Journal Magazine Jul 2017 - Dec 2017
> 
> Below are steps to creating one. First create a folder on an available
> disk in your filesystem using an filesystem server administrative login;
> Next give full rights to the postgres server account (the one the daemon
> process runs under) or you can change the owner of the folder to the
> postgres account (in linux you can use chown postgres and on windows
> just use the administrative properties ...
> www.postgresonline.com
>
>
>
> 
> *From:* Adrian Klaver 
> *Sent:* Monday, May 13, 2019 2:47 PM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 1:26 PM, Julie Nishimura wrote:
>> Adrian, thanks for your reply. Couple of clarifications/questions:
>> 1) we are on 8.3 for this server
>
> 8.3 does not have ALTER DATABASE name SET TABLESPACE :
>
> https://www.postgresql.org/docs/8.3/sql-alterdatabase.html
>
> It does appear until 8.4.
>
>
>> 2) I was under impression, that "ALTER DATABASE name SET TABLESPACE
>> new_tablespace" won't move anything, and just utilize the new_tablespace
>> for new tables/indexes. If we would want to move existing tables, we
>> would need to move them one by one by
>
> No think of ALTER DATABASE name SET TABLESPACE  as a bulk operation of
> ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
> across all existing tables.  This assumes the all existing tables live
> in the  current default tablespace. It is moot in your case as 8.3 is
> not capable of doing this.
>
>> ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
>> /
>> /
>
> The above is your option in 8.3.
>
>> 3) I thought if I want to have any newly created dbs go to the new vol,
>> I need to alter template.
>
> It is not required you can spec the tablespace in the CREATE DATABASE
> command:
>
> https://www.postgresql.org/docs/8.3/sql-createdatabase.html
>
> Though you can move them to make the tablespace the default. See above link.
>
>
>> 4) I was also thinking about changing parameters in config file to point
>> to the newly volume and reload postgresql.conf
>>
>> default_tablespace
>>
>> temp_tablespaces
>
> You could that. If you do it, moving the template tables would be redundant.
>
>>
>>
>> Am I wrong here?
>>
>>
>> Thanks,
>>
>> Julie
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Adrian Klaver]

On 5/13/19 4:53 PM, Julie Nishimura wrote:

Adrian,
I think in my first emails in this chain about the effort i did 
specified it properly:


CREATE TABLESPACE vol4

OWNER postgres
LOCATION '/data/vol4';

ALTER DATABASE user_db_1
SET default_tablespace = 'vol4';

...for all dbs..


Then "default" part got lost in emails...:)


Well that was a stupid on my part:(

To add insult to injury:

"No, what the link showed is:

ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace

That is is the same as changing default_tablespace in postgresql.conf.
"

is wrong also. What the above does is change the behavior for that 
database only.






*From:* Adrian Klaver 
*Sent:* Monday, May 13, 2019 4:33 PM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: does postgresql backup require additional space on disk
On 5/13/19 3:27 PM, Julie Nishimura wrote:
Adrian, thanks for your reply. I previously found this link, which 
mentioned 8.2, that is why I assumed it will work with 8.3.


No, what the link showed is:

ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace

That is is the same as changing default_tablespace in postgresql.conf.

It is not the same as:

ALTER DATABASE name SET TABLESPACE



Too bad we don't have dev with the same version. I guess I will create 


You can use the same commands on dev to test, just not ALTER DATABASE
name SET TABLESPACE as that is not in 8.3.

new tablespace using new vol, move the smallest db for which I have 


You cannot move a db as a unit, you will need to move the individual
tables/indexes in the db.

backup, create new test table and examine new/existing tables if they 
have moved by selecting tablespace from pg_tables... right?


Assuming you either specify the new tablespace on table CREATE or have
set default_tablespace to the new tablespace.




http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html
Managing disk space using table spaces - Postgres OnLine Journal - 
Postgres OnLine Journal Magazine Jul 2017 - Dec 2017 

Below are steps to creating one. First create a folder on an available 
disk in your filesystem using an filesystem server administrative login; 
Next give full rights to the postgres server account (the one the daemon 
process runs under) or you can change the owner of the folder to the 
postgres account (in linux you can use chown postgres and on windows 
just use the administrative properties ...

www.postgresonline.com 




*From:* Adrian Klaver 
*Sent:* Monday, May 13, 2019 2:47 PM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: does postgresql backup require additional space on disk
On 5/13/19 1:26 PM, Julie Nishimura wrote:

Adrian, thanks for your reply. Couple of clarifications/questions:
1) we are on 8.3 for this server


8.3 does not have ALTER DATABASE name SET TABLESPACE :

https://www.postgresql.org/docs/8.3/sql-alterdatabase.html

It does appear until 8.4.


2) I was under impression, that "ALTER DATABASE name SET TABLESPACE 
new_tablespace" won't move anything, and just utilize the new_tablespace 
for new tables/indexes. If we would want to move existing tables, we 
would need to move them one by one by


No think of ALTER DATABASE name SET TABLESPACE  as a bulk operation of
ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
across all existing tables.  This assumes the all existing tables live
in the  current default tablespace. It is moot in your case as 8.3 is
not capable of doing this.


ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
/
/


The above is your option in 8.3.

3) I thought if I want to have any newly created dbs go to the new vol, 
I need to alter template.


It is not required you can spec the tablespace in the CREATE DATABASE
command:

https://www.postgresql.org/docs/8.3/sql-createdatabase.html

Though you can move them to make the tablespace the default. See above link.


4) I was also thinking about changing parameters in config file to point 
to the newly volume and reload postgresql.conf


default_tablespace

temp_tablespaces


You could that. If you do it, moving the template tables would be redundant.




Am I wrong here?


Thanks,

Julie





--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Julie Nishimura]

LOL. Adrian, I so appreciate your help.

So, since this server is obviously very old, and it seems like 3 other 
tablespaces were added in the past (vol1 - 2.7 tb, vol2 - 2.7 tb, vol3 - 16 
tb), and I am about to add another vol - vol4 (4 tb), I am trying to see where 
the objects are actually located.

So, for example, working only with one database at a time.

The database has the following:
ALTER DATABASE er_temp
  SET default_tablespace = 'vol3';

My postgresql.conf also lists the following:
default_tablespace = 'vol3'

There is one table "test", and its DDL (according to PGAdmin):
CREATE TABLE test
(
  id integer,
  key character varying,
  value character varying
)
WITH (
  OIDS=FALSE
)
TABLESPACE vol1;

but if I run the following command, I have empty string as tablespace

er_temp=# SELECT tablespace
FROM pg_tables
WHERE tablename = 'test' AND schemaname = 'public';
 tablespace


(1 row)

However, if I create new table, it will go to vol3:

er_temp=# CREATE TABLE test_j
er_temp-# (
er_temp(#   id integer,
er_temp(#   key character varying,
er_temp(#   value character varying
er_temp(# );
CREATE TABLE
er_temp=# SELECT tablespace
FROM pg_tables
WHERE tablename = 'test_j' AND schemaname = 'public';
 tablespace

 vol3
(1 row)

So, why would tablespace for "test" show as empty string if it is not default? 
Where are the files for "test" table?

Thanks,
Julie

From: Adrian Klaver 
Sent: Monday, May 13, 2019 5:09 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk

On 5/13/19 4:53 PM, Julie Nishimura wrote:
> Adrian,
> I think in my first emails in this chain about the effort i did
> specified it properly:
>
> CREATE TABLESPACE vol4
>> OWNER postgres
>> LOCATION '/data/vol4';
>>
>> ALTER DATABASE user_db_1
>> SET default_tablespace = 'vol4';
>>
>> ...for all dbs..
>
> Then "default" part got lost in emails...:)

Well that was a stupid on my part:(

To add insult to injury:

"No, what the link showed is:

ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace

That is is the same as changing default_tablespace in postgresql.conf.
"

is wrong also. What the above does is change the behavior for that
database only.

>
>
> 
> *From:* Adrian Klaver 
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 3:27 PM, Julie Nishimura wrote:
>> Adrian, thanks for your reply. I previously found this link, which
>> mentioned 8.2, that is why I assumed it will work with 8.3.
>
> No, what the link showed is:
>
> ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace
>
> That is is the same as changing default_tablespace in postgresql.conf.
>
> It is not the same as:
>
> ALTER DATABASE name SET TABLESPACE
>
>>
>> Too bad we don't have dev with the same version. I guess I will create
>
> You can use the same commands on dev to test, just not ALTER DATABASE
> name SET TABLESPACE as that is not in 8.3.
>
>> new tablespace using new vol, move the smallest db for which I have
>
> You cannot move a db as a unit, you will need to move the individual
> tables/indexes in the db.
>
>> backup, create new test table and examine new/existing tables if they
>> have moved by selecting tablespace from pg_tables... right?
>
> Assuming you either specify the new tablespace on table CREATE or have
> set default_tablespace to the new tablespace.
>
>>
>>
>> http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html
>> Managing disk space using table spaces - Postgres OnLine Journal -
>> Postgres OnLine Journal Magazine Jul 2017 - Dec 2017
>> 
>> Below are steps to creating one. First create a folder on an available
>> disk in your filesystem using an filesystem server administrative login;
>> Next give full rights to the postgres server account (the one the daemon
>> process runs under) or you can change the owner of the folder to the
>> postgres account (in linux you can use chown postgres and on windows
>> just use the administrative properties ...
>> www.postgresonline.com 
>> 
>>
>>
>>
>> 
>> *From:* Adrian Klaver 
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
>> *Subject:* Re: does postgresql backup require additional space on disk
>> On 5/13/19 1:26 PM, Julie Nishimura wrote:
>>> Adrian, thanks for your reply. Couple of clarifications/questions:
>>> 1) we are on 8.3 for this server
>>
>> 8.3 does not have ALTER DATABASE name SE

Re: does postgresql backup require additional space on disk

[Adrian Klaver]

On 5/13/19 5:39 PM, Julie Nishimura wrote:

LOL. Adrian, I so appreciate your help.

So, since this server is obviously very old, and it seems like 3 other 
tablespaces were added in the past (vol1 - 2.7 tb, vol2 - 2.7 tb, vol3 - 
16 tb), and I am about to add another vol - vol4 (4 tb), I am trying to 
see where the objects are actually located.


So, for example, working only with one database at a time.

The database has the following:
ALTER DATABASE er_temp
   SET default_tablespace = 'vol3';

My postgresql.conf also lists the following:
default_tablespace = 'vol3'

There is one table "test", and its DDL (according to PGAdmin):
CREATE TABLE test
(
   id integer,
   key character varying,
   value character varying
)
WITH (
   OIDS=FALSE
)
TABLESPACE vol1;

but if I run the following command, I have empty string as tablespace

er_temp=# SELECT tablespace
FROM pg_tables
WHERE tablename = 'test' AND schemaname = 'public';
  tablespace


(1 row)


Is test in the public schema?



However, if I create new table, it will go to vol3:

er_temp=# CREATE TABLE test_j
er_temp-# (
er_temp(#   id integer,
er_temp(#   key character varying,
er_temp(#   value character varying
er_temp(# );
CREATE TABLE
er_temp=# SELECT tablespace
FROM pg_tables
WHERE tablename = 'test_j' AND schemaname = 'public';
  tablespace

  vol3
(1 row)

So, why would tablespace for "test" show as empty string if it is not 
default? Where are the files for "test" table?


Thanks,
Julie




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Julie Nishimura]

er_temp=# select * from pg_tables where tablename = 'test';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | 
hastriggers
+---++++--+-
 public | test  | build  || f  | f| f
(1 row)


From: Adrian Klaver 
Sent: Monday, May 13, 2019 5:52 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk

On 5/13/19 5:39 PM, Julie Nishimura wrote:
> LOL. Adrian, I so appreciate your help.
>
> So, since this server is obviously very old, and it seems like 3 other
> tablespaces were added in the past (vol1 - 2.7 tb, vol2 - 2.7 tb, vol3 -
> 16 tb), and I am about to add another vol - vol4 (4 tb), I am trying to
> see where the objects are actually located.
>
> So, for example, working only with one database at a time.
>
> The database has the following:
> ALTER DATABASE er_temp
>SET default_tablespace = 'vol3';
>
> My postgresql.conf also lists the following:
> default_tablespace = 'vol3'
>
> There is one table "test", and its DDL (according to PGAdmin):
> CREATE TABLE test
> (
>id integer,
>key character varying,
>value character varying
> )
> WITH (
>OIDS=FALSE
> )
> TABLESPACE vol1;
>
> but if I run the following command, I have empty string as tablespace
>
> er_temp=# SELECT tablespace
> FROM pg_tables
> WHERE tablename = 'test' AND schemaname = 'public';
>   tablespace
> 
>
> (1 row)

Is test in the public schema?

>
> However, if I create new table, it will go to vol3:
>
> er_temp=# CREATE TABLE test_j
> er_temp-# (
> er_temp(#   id integer,
> er_temp(#   key character varying,
> er_temp(#   value character varying
> er_temp(# );
> CREATE TABLE
> er_temp=# SELECT tablespace
> FROM pg_tables
> WHERE tablename = 'test_j' AND schemaname = 'public';
>   tablespace
> 
>   vol3
> (1 row)
>
> So, why would tablespace for "test" show as empty string if it is not
> default? Where are the files for "test" table?
>
> Thanks,
> Julie



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: does postgresql backup require additional space on disk

[Adrian Klaver]

On 5/13/19 5:56 PM, Julie Nishimura wrote:

er_temp=# select * from pg_tables where tablename = 'test';
  schemaname | tablename | tableowner | tablespace | hasindexes | 
hasrules | hastriggers

+---++++--+-
  public     | test      | build      |            | f          | f 
    | f

(1 row)



Alright.

I don't have time at the moment to create a query, but I would look at 
using pg_class(relkind='r' for tables):


https://www.postgresql.org/docs/8.3/catalog-pg-class.html

and joining to pg_tablespace:

https://www.postgresql.org/docs/8.3/catalog-pg-tablespace.html

Order by the tablespace name to see where the tables are located.

--
Adrian Klaver
adrian.kla...@aklaver.com

psql dones't reflect exit status if input command via stdin

[magodo]

Hello,

I found when running command like `# echo "xxx" | psql postgres
postgres`, the return code is always 0 even though the command ("xxx")
here is of invalid syntax. While the `psql -c` way handled exit code
correctly.

In the meanwhile, mysql client handled both cases correctly.

---
Zhaoting.Weng

Re: psql dones't reflect exit status if input command via stdin

[David G. Johnston]

On Mon, May 13, 2019 at 11:24 PM magodo  wrote:

> I found when running command like `# echo "xxx" | psql postgres
> postgres`, the return code is always 0 even though the command ("xxx")
> here is of invalid syntax. While the `psql -c` way handled exit code
> correctly.
>

Its only required to handle things as documented, which this is:

"psql returns 0 to the shell if it finished normally, 1 if a fatal error of
its own occurs (e.g. out of memory, file not found), 2 if the connection to
the server went bad and the session was not interactive, and 3 if an error
occurred in a script and the variable ON_ERROR_STOP was set."

The pipe-version causes psql to execute stdin as a script.  By default
ON_ERROR_STOP is unset.  Thus psql finished processing the script normally
and while it encountered an error it continued past the error as opposed to
stopping with exit code 3.

Adding "\set ON_ERROR_STOP 1" to your .psqlrc will result in psql behaving
in the way you expect.

David J.