Re: [GENERAL] PK Index - Removal
On Thu, Aug 11, 2016 at 1:30 PM, Patrick B wrote:
> Hi guys,
>
> I got the following index:
>
> CREATE INDEX "ix_mo_pk" ON "mo" USING "btree" ((("id")::"text"))
>
>
> The "ID" is my primary key:
>
>> "mo_pkey" PRIMARY KEY, "btree" ("id")
>
>
> The ix_mo_pk index is not being used... But as it has the "ID" on it, I
> wanted to ask before dropping it.
>
> Can you guys explain me if the ix_mo_pk is safe to delete and why?
>
> The mo_pkey index is being used.
>
Yes, you can drop the unused Indexes provided they are not the ones created
by primary key or unique key constraints. By your explanation, it seems
that the index "ix_mo_pk" is an user defined index (created by you), so, it
can be dropped if not used.
Regards,
Venkata B N
Fujitsu Australia
Re: [GENERAL] Logical Decoding Failover
> Now the logical decoding client connects to B (the new primary). The > replication slot doesn't exist. So, it creates it and starts streaming. > This is where the problem lies - as it would begin streaming from LSN 4 > (anything after what has already been committed), because I have no way > (that I can find) of restoring my "progress" through the WAL on a the > replicas. > > As a result, my application never sees the event at LSN 3. In fact, I'm > not even sure how I could manually do this. > Yes, that is the current limitation and as of i know, there is no such patch being developed at the moment. I leave it to any of the developers to respond. This cannot be done manually as the new master will not read through from LSN 3 (which is a thing of past unknown to new master) and replication slot at standby is a new one with the latest starting from LSN 4 which, the logical decoder does not know. Hope that helps ! Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Logical Decoding Failover
On Sun, Aug 7, 2016 at 9:29 PM, Colin Morelli wrote: > Venkata, > > Thanks for the reply. Unfortunately something like PgPool still won't > create the replication slots on all hosts, and record the LSN in a way that > is reusable on the secondary. > Yes, thats correct, pgPool does not have anything to do with replication slots. That is something which you need to manually configure. > This sort of puts logical decoding at odds with HA, correct? In the case > of master failover, there's no way to: a) know where in the stream you read > to, or b) convert that to something that can be understood by a replica > that's about to be promoted. > Can you please clarify, what did you exactly mean here ? are you referring to cascading standbys or something ? Failover is something which application has to do, to reconnect to the promoted standby. As far as logical decoding is concerned, you need to have a replication slot configured for the new master to ensure all the WALs which are not replicated to standby are retained. After promotion, the standby database becomes a standalone instance and the replication must be re-enabled. You can automate the standby promotion using tools like pgpool-II or pacemaker. I am trying to differentiate standby-promotion and failover mechanism here. Are there any scheduled improvements here either for 9.6 or 9.7? > I am not 100% sure, if there is anything developed from an failover mechanism perspective. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Logical Decoding Failover
On Sat, Aug 6, 2016 at 1:17 PM, Colin Morelli wrote: > Hey all, > > I'm using logical decoding in my application to capture change streams and > ship them to Kafka. However, logical replication slots aren't included in > the WAL and thus don't make it to replicas. In the case of a failover, it's > not clear what (if anything) can be done to continue receiving a change > stream. > > I noticed there was a failover slot patch that looks like it won't be > making it for 9.6 (if at all), but does anyone else have suggestions for > handling failover + logical decoding? Is there an answer, or is it limited > to being a very manual process for now? > Yes, as of i know, It is a manual process for now. Another alternative you can consider (if possible at all) would be a tool like PgPool-II which is actually used for connection pooling, load balancing and automatic failover. It may not make sense if you are do not wish to pool you connections. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Question about wal files / pg_xlogs
>
> I use a bash script to ship them. The script hasn't being changed So
> it isn't the problem.
>
>
> *postgresql.conf:*
>
>> archive_command = 'exec nice -n 19 ionice -c 2 -n 7 archive_command.bash
>> "%p" slave01 slave02'
>
> *archive_command.bash:*
>
> Basically we use TAR to ship through ssh:
>
>> # we use tar over SSH as I don't fully trust scp's exit status. The added
>> benefit is that tar preserves all attributes
>> # the downside is that it's a little tricky to make the remote path
>> relative
>> tar -c -O --no-same-owner -C "${WAL_SEGMENT%/*}" "${WAL_SEGMENT##*/}" |
>> ssh -p ${NEXT_PORT} -C -o 'BatchMode=yes' -o 'CompressionLevel=3'
>> "${USER}@${NEXT_HOST}"
>> "exec tar -x --no-same-owner --overwrite -C '${WAL_ARCHIVE_PATH}'";
>> PS_CONCAT="${PIPESTATUS[*]}";
>
>
> The script is complex, but as I said, nothing has been changed on it.
>
Not sure why the script is so complex. Do you see any messages in the
postgresql log file on master ? and on slave ? which indicates the reason
for delayed shipping of WAL archives. Did you notice any network level
issues ?
Regards,
Venkata B N
Fujitsu Australia
Re: [GENERAL] How to best archetect Multi-Tenant SaaS application using Postgres
> > How many concurrent connections can be made, will purely depend on number > of CPUs (cores) you have available on the database server. > Well, certainly 1 would be the way to go to build an multi-tenant > application, but, it strongly depends on your application specific > requirements and how are you distributing the data across databases and how > the users are accessing data across the databases. > > Yup, I also would like to go with 1, suppose a server with 16 core, 32GB > memory, SSD box, how many connections could it handle roughly, e.g. O(100) > or O(1000) or O(5000)? > Well, as its been said already, you can typically have 16 concurrent connections at a time and more if the CPUs are hyper-threading enabled. All that said, how many requests your database can serve will depend on the fact that, how long each connection is expected to spend on the database. This means, there must an appropriate benchmarking process in place. If you really see a connection/query spending few milli-seconds on the database with some aggressive connection pooling mechanism, then you can get more number of requests served in less time. Generally, it is possible that, 1000s of requests can be served and those being concurrent will be far from real with kind of hardware capacity you have. So, the solution would be to have appropriate tuning and benchmarking process in place. Regards, Venkata B N Fujitsu, Australia > On August 1, 2016 at 10:30:48 PM, Venkata Balaji N (nag1...@gmail.com) > wrote: > > > On Sun, Jul 31, 2016 at 12:07 PM, Silk Parrot > wrote: > >> Hi, >> >> We are trying build a multi tenant application and are debating which >> approach we should take: (also my understanding is based on that pgbouncer >> connection pool doesn’t work across different user/database pair): >> >> 1. For each tenant, we create a dedicated database and a dedicated user. >> This option gives best isolation. However, connection pooling won’t work >> pgbouncer. >> > > Not sure what you meant by saying connection pooling (pgBouncer) does not > work ? This is the general approach for building a multi-tenant application. > >> >> 2. We put all tenants in a single database, and just use one user. I >> learned that pgbackup will be probamatic when there are > 30 schemas in a >> database, so we probably can’t create dedicate schema for each tenant. >> >> We are more inclined to choose 1, but don't know how many concurrent >> connections Postgres can handle for OLTP workload in a 32GB memory, SSD >> box. Also we would like hear from someone with more postgres experience >> about the best practice for building multi-tenant application. Again, i am >> not sure what you meant by saying pgbackup will not work where there are >> more than 30 schemas ? >> > > Which version of PostgreSQL are you using ? > > How many concurrent connections can be made, will purely depend on number > of CPUs (cores) you have available on the database server. > Well, certainly 1 would be the way to go to build an multi-tenant > application, but, it strongly depends on your application specific > requirements and how are you distributing the data across databases and how > the users are accessing data across the databases. > > Regards, > Venkata B N > > Fujitsu Australia > >
Re: [GENERAL] How to best archetect Multi-Tenant SaaS application using Postgres
On Sun, Jul 31, 2016 at 12:07 PM, Silk Parrot wrote: > Hi, > > We are trying build a multi tenant application and are debating which > approach we should take: (also my understanding is based on that pgbouncer > connection pool doesn’t work across different user/database pair): > > 1. For each tenant, we create a dedicated database and a dedicated user. > This option gives best isolation. However, connection pooling won’t work > pgbouncer. > Not sure what you meant by saying connection pooling (pgBouncer) does not work ? This is the general approach for building a multi-tenant application. > > 2. We put all tenants in a single database, and just use one user. I > learned that pgbackup will be probamatic when there are > 30 schemas in a > database, so we probably can’t create dedicate schema for each tenant. > > We are more inclined to choose 1, but don't know how many concurrent > connections Postgres can handle for OLTP workload in a 32GB memory, SSD > box. Also we would like hear from someone with more postgres experience > about the best practice for building multi-tenant application. Again, i am > not sure what you meant by saying pgbackup will not work where there are > more than 30 schemas ? > Which version of PostgreSQL are you using ? How many concurrent connections can be made, will purely depend on number of CPUs (cores) you have available on the database server. Well, certainly 1 would be the way to go to build an multi-tenant application, but, it strongly depends on your application specific requirements and how are you distributing the data across databases and how the users are accessing data across the databases. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Replication with non-read-only standby.
On Thu, Jun 30, 2016 at 11:15 PM, Nick Babadzhanian wrote: > Setup: > 2 PostgreSQL servers are geographically spread. The first one is used for > an application that gathers data. It is connected to the second database > that is used to process the said data. Connection is not very stable nor is > it fast, so using Bidirectional replication is not an option. It is OK if > data is shipped in batches rather than streamed. > > Question: > Is there a way to make the standby server non-read-only, so that it can > keep getting updates (mostly inserts) from the 'master', but users are able > to edit the data stored on 'slave'? Is there some alternative solution to > this? > You can consider Ruby replication for such a requirement. I think, there is no much development happening around Ruby Replication since long time i believe. This can be used for production environment. http://www.rubyrep.org/ Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Slony error please help
On Fri, Jun 17, 2016 at 5:36 AM, avi Singh wrote: > I am working on a project to upgrade postgresql from 9.4 to 9.5 we use > slony for replication > > > Phase 1 problem > > 1. We had slony version slony1-94-2.2.2-1.rhel5.x86_64 installed when i > tried testing upgrade on my test env it failed coz slony 2.2.2 version is > not compatible with 9.5 so i upgraded slony1-94-2.2.2-1.rhel5.x86_64 > to slony1-94-2.2.4-4.rhel5.x86_64 which went well. > > > Phase 2 problem > > 1. Back to upgrade testing now when i run the check_upgrade script which > includes the command below > > /usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.4/bin/ -B > /usr/pgsql-9.5/bin/ -d /var/lib/pgsql/cmates1/data/ -D > /var/lib/pgsql/cmates1/data/9.5/ -p 5432 -P 5433 -c > > > it throws this error > > Could not load library "$libdir/slony1_funcs.2.2.2" > ERROR: could not access file "$libdir/slony1_funcs.2.2.2": No such file > or directory > How did you upgrade Slony ? Uninstall Slony, drop slony related schemas and re-install, re-configure slony from scratch ? or did you ensure SLONIK_UPDATE_FUNCTIONS has been executed successfully ? Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] High availability and load balancing ...
On Thu, Jun 9, 2016 at 8:01 PM, Sunil N Shinde wrote: > Thanks Venkata. > > > > I am considering latest version now i.e. 9.4 or 9.5 on Linux 6. > > Is there any difference in setup from 9.1 to 9.5? > There is no difference in the setup. Streaming Replication in the version 9.5 is a lot better with a lot of bug fixes specific to streaming replication and with a few extra parameters compared to the version 9.1. Please refer to postgresql documentation. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] High availability and load balancing ...
> > > > I need to do the setup for High availability function. > > Also want to implement load balancing for 02 nodes. > You will have to build streaming replication which was introduced in PostgreSQL-9.0 > I think PGPool will be require for that. Can I use PGPool without cost. > pgpool-II is an open source tool which can be used for connection pooling and load balancing. > Can I get the basic steps to do this setup? > > > > Database-- Postgresql 9.1 > > OS -- Linux 6 > Below is the link which explains the basic steps to setup "streaming replication" https://www.postgresql.org/docs/9.1/static/warm-standby.html By the way, version 9.1 is very old and will reach end-of-life soon. You are 4 major versions behind, did you consider using latest version ? Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Slave claims requested WAL segment already removed - but it wasn't
On Thu, Jun 2, 2016 at 11:43 AM, Jeff Beck wrote: > Hi- > We have a master (pg 9.4.4 on Ubuntu 14.10) and a slave (pg 9.4.8 on > Centos 7). During a period of heavy use, the slave began complaining > that the “requested WAL segment xx has already been removed”. But > the WAL segment was still on the master. The issue was resolved by > manually copying the pg_xlog directory over to the slave. > > I don’t see any errors on the master log file, or any other messages on > the slave’s. What happened? How can this be prevented in the future? > If you are using streaming replication, then, enabling replication slots will ensure all the WALs which are not replayed to slave will be retained at master. Having said that, your situation seems a bit strange to me. Slave cannot complain about an existing WAL segment at master, can you just give a bit more details about your replication architecture ? Slave is unable to read the required WAL segment for some reason ? Do you have this situation happening regularly ? Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] After replication failover: could not read block X in file Y read only 0 of 8192 bytes
Oops, i missed including pgsql-general in my earlier replies.. > > > I have data_checksums switched on so am suspecting a streaming > > > > > replication bug. Anyone know of a recent bug which could have > caused > > > > > this? > > > > > > > > > > > > > I cannot conclude at this point. I encountered these kind of errors > with > > > > Indexes and re-indexing fixed them. > > > > > > This is actually the second time I am seeing these kinds of errors, in > > > the past, after verifying that no data was lost I used VACUUM FULL to > > > recover the ability to INSERT. There was no pitchfork uprising... > > > > > > > Did you check your disk file systems for any issues ? > > I'm on EC2 and praying that data_checksums takes care of that for me... > Please check if any issues with the storage. > > These errors only > > happen after you do pg_restore ? > > they happened after the failover, I'm not convinced they were caused by > the pg_restore. > mmh ok. Any issues on slave before failover ? > > I still have the backups and WAL logs and could probably try use them to > build a bad slave. But that's a few hundred GB of WAL and is not worth > it unless I know what to look for. > Well, that is the work around if you are encountering data loss due to this bug. Developers must be able to tell you if this is a bug in streaming replication. I haven't encountered such bug till now. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Checkpoint Err on Startup of Rsynced System
On Wed, Jun 1, 2016 at 3:13 AM, Jim Longwill wrote:
> I am trying to setup a 2nd, identical, db server (M2) for development and
> I've run into a problem with starting up the 2nd Postgres installation.
>
> Here's what I've done:
> 1) did a 'clone' of 1st (production) machine M1 (so both machines on
> Cent OS 7.2)
> 2) setup an rsync operation, did a complete 'rsync' from M1 to M2
> 3) did a final 'CHECKPOINT' command on M1 postgres
> 4) shutdown postgres on M1 with 'pg_ctl stop'
> 5) did final 'rsync' operation (then restarted postgres on M1 with
> 'pg_ctl start')
> 6) tried to startup postgres on M2
>
If you rsync the data-directory of an live running postgres instance, that
is not going to work. As Scott said earlier, you need to do "select
pg_start_backup('labelname');" before you initiate rsync and "select
pg_stop_backup()" after you complete rsync. That way, postgresql would know
that you are rsyncing and also identifies the required WALs to be copied
over.
Or if you can shutdown M1 for sometime then, simply shutdown M1 copy over
(or rsync) the data-directory to M2 and then start the M2 instance. That
should work.
Regards,
Venkata B N
Fujitsu Australia
Re: [GENERAL] After replication failover: could not read block X in file Y read only 0 of 8192 bytes
On Mon, May 30, 2016 at 11:37 PM, Brian Sutherland wrote: > I'm running a streaming replication setup with PostgreSQL 9.5.2 and have > started seeing these errors on a few INSERTs: > > ERROR: could not read block 8 in file "base/3884037/3885279": read > only 0 of 8192 bytes > These errors are occurring on master or slave ? > on a few tables. If I look at that specific file, it's only 6 blocks > long: > > # ls -la base/3884037/3885279 > -rw--- 1 postgres postgres 49152 May 30 12:56 base/3884037/3885279 > > It seems that this is the case on most tables in this state. I havn't > seen any error on SELECT and I can SELECT * on the all tables I know > have this problem. The database is machine is under reasonable load. > So, the filenodes generating this error belong to a Table ? or an Index ? > On some tables an "ANALYZE tablename" causes the error. > > We recently had a streaming replication failover after loading a large > amount of data with pg_restore. The problems seem to have started after > that, but I'm not perfectly sure. > pg_restore has completed successfully ? When pg_restore was running, did you see anything suspicious in the postgresql logfiles ? I have data_checksums switched on so am suspecting a streaming > replication bug. Anyone know of a recent bug which could have caused > this? > I cannot conclude at this point. I encountered these kind of errors with Indexes and re-indexing fixed them. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] postgresql-9.5.3 compilation on Solaris SPARC
On Sun, May 22, 2016 at 6:38 PM, Venkata Balaji N wrote:
>
> On Sun, May 22, 2016 at 1:59 AM, Tom Lane wrote:
>
>> Venkata Balaji N writes:
>> > On Sat, May 21, 2016 at 1:04 AM, Tom Lane wrote:
>> >> http://www.unix.com/man-page/opensolaris/3c/atomic_cas/
>> >> http://docs.oracle.com/cd/E23824_01/html/821-1465/atomic-cas-3c.html
>> >>
>> >> I see that the first of those mentions it's for SunOS 5.11 whereas your
>> >> machine says it's 5.10 ... is it possible the functions were new in
>> 5.11?
>>
>> > I am not 100% sure. By the error, what i understand is that for some
>> reason
>> > PostgreSQL version 9.5.x is expecting the SunOS version to be more
>> recent
>> > than 5.10.
>>
>> Well, it's not --- we have a buildfarm animal that's running 5.10, and
>> it seems perfectly happy:
>>
>> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=castoroides&dt=2016-05-21%2014%3A00%3A47
>>
>> At this point I suspect that Asif Naeem was correct upthread in
>> questioning your compile options --- particularly the -L switches.
>> castoroides seems to just be using this:
>>
>> 'config_env' => {
>> 'CFLAGS' => '-m64 -xarch=native -xdepend -xO4
>> -xprefetch=auto,explicit',
>> 'CC' => 'cc',
>> 'LDFLAGS' => ''
>> },
>>
>
> Thanks. Then, as i suspected, the problem must be around using appropriate
> CFLAGS, which i am not sure at what to use. One thing i can say at this
> point is that, if i just add "-m64" option in CFLAGS, i get an error
> illegal option.
> I will re-test the compilation and get back with the inputs.
>
All good now. I am able to get the PostgreSQL-9.5.3 64-Bit installation
done by removing the CFLAGS. Below is the configure command which i used -
./configure --prefix=/opt/postgres/9.5.3 CC="gcc -m64" --enable-cassert
--without-readline --enable-thread-safety
Thanks all for your inputs and sorry for the confusion.
Regards,
Venkata B N
Fujitsu Australia
Re: [GENERAL] postgresql-9.5.3 compilation on Solaris SPARC
Hi Asif, Sorry, i should have responded first. Thanks for inputs and my replies are inline - > Any help would be appreciated. Do i need to give any particular CFLAGS ? >> >> >> Below is the *configure *command - >> >> >> ./configure --prefix=/opt/postgres/9.5.3 CC=/opt/SUNWspro/bin/cc >> 'CFLAGS=-xO3 -xarch=v9 -xspace -W0,-Lt -W2,-Rcond_elim -Xa -xildoff >> -xc99=none -xCC' --enable-cassert --without-readline --enable-thread-safety >> LDFLAGS='-L/usr/lib/sparcv9 -L/usr/ucblib/sparcv9' >> > > AFAIR I deprived use of -xarch=v9 and used -m64 instead. I wonder why are > you using LDFLAGS='-L/usr/lib/sparcv9 -L/usr/ucblib/sparcv9', when you > provide -xarch=v9 compiler should automatically pick the correct libraries > and headers I think. There seems lot of cflags used, are you using them > because of any recommendation or any issue faced ?. > I just gave LDFLAGS in-case compiler cannot pickup the libraries and the locations i gave in LDFLAGS is for 64 Bit libraries. "-m64" in CFLAGS is generating an error and CC="gcc -m64" is running successfully. So, no issues there. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] postgresql-9.5.3 compilation on Solaris SPARC
On Sun, May 22, 2016 at 1:59 AM, Tom Lane wrote:
> Venkata Balaji N writes:
> > On Sat, May 21, 2016 at 1:04 AM, Tom Lane wrote:
> >> http://www.unix.com/man-page/opensolaris/3c/atomic_cas/
> >> http://docs.oracle.com/cd/E23824_01/html/821-1465/atomic-cas-3c.html
> >>
> >> I see that the first of those mentions it's for SunOS 5.11 whereas your
> >> machine says it's 5.10 ... is it possible the functions were new in
> 5.11?
>
> > I am not 100% sure. By the error, what i understand is that for some
> reason
> > PostgreSQL version 9.5.x is expecting the SunOS version to be more recent
> > than 5.10.
>
> Well, it's not --- we have a buildfarm animal that's running 5.10, and
> it seems perfectly happy:
>
> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=castoroides&dt=2016-05-21%2014%3A00%3A47
>
> At this point I suspect that Asif Naeem was correct upthread in
> questioning your compile options --- particularly the -L switches.
> castoroides seems to just be using this:
>
> 'config_env' => {
> 'CFLAGS' => '-m64 -xarch=native -xdepend -xO4
> -xprefetch=auto,explicit',
> 'CC' => 'cc',
> 'LDFLAGS' => ''
> },
>
Thanks. Then, as i suspected, the problem must be around using appropriate
CFLAGS, which i am not sure at what to use. One thing i can say at this
point is that, if i just add "-m64" option in CFLAGS, i get an error
illegal option.
I will re-test the compilation and get back with the inputs.
Regards,
Venkata B N
Re: [GENERAL] postgresql-9.5.3 compilation on Solaris SPARC
On Sat, May 21, 2016 at 1:04 AM, Tom Lane wrote: > Venkata Balaji N writes: > > "make" command is generating the following error while compiling > > postgresql-9.5.3 on Solaris SPARC. > > > Undefined first referenced > > symbol in file > > atomic_cas_64 port/atomics.o > > atomic_cas_32 port/atomics.o > > Hmm. Do you get any warnings about references to those functions earlier > in the build? > I do not see any such similar warnings earlier in the build. src/include/port/atomics/generic-sunpro.h cites these references > as authority for believing that those functions exist in Solaris: Yes, i could see the references mentioned in the above file. > >http://www.unix.com/man-page/opensolaris/3c/atomic_cas/ >http://docs.oracle.com/cd/E23824_01/html/821-1465/atomic-cas-3c.html > > I see that the first of those mentions it's for SunOS 5.11 whereas your > machine says it's 5.10 ... is it possible the functions were new in 5.11? > I am not 100% sure. By the error, what i understand is that for some reason PostgreSQL version 9.5.x is expecting the SunOS version to be more recent than 5.10. We would need to compile PostgreSQL-9.5.3 to upgrade our customer's production environments. We did not have problems compiling earlier versions of PostgreSQL. Regards, Venkata B N Fujitsu Australia
[GENERAL] postgresql-9.5.3 compilation on Solaris SPARC
Hi, "make" command is generating the following error while compiling postgresql-9.5.3 on Solaris SPARC. I tried compiling 9.2 and 9.3, works fine. This is only happening on 9.5. ../../src/port/libpgport_srv.a ../../src/common/libpgcommon_srv.a -lnsl -lrt -lsocket -lm -o postgres Undefined first referenced symbol in file atomic_cas_64 port/atomics.o atomic_cas_32 port/atomics.o ld: fatal: Symbol referencing errors. No output written to postgres make[2]: *** [postgres] Error 1 make[2]: Leaving directory `/opt/postgresql-9.5.3/src/backend' make[1]: *** [all-backend-recurse] Error 2 make[1]: Leaving directory `/opt/postgresql-9.5.3/src' make: *** [all-src-recurse] Error 2 Any help would be appreciated. Do i need to give any particular CFLAGS ? Below is the *configure *command - ./configure --prefix=/opt/postgres/9.5.3 CC=/opt/SUNWspro/bin/cc 'CFLAGS=-xO3 -xarch=v9 -xspace -W0,-Lt -W2,-Rcond_elim -Xa -xildoff -xc99=none -xCC' --enable-cassert --without-readline --enable-thread-safety LDFLAGS='-L/usr/lib/sparcv9 -L/usr/ucblib/sparcv9' I tried adding an option CFLAGS_SSE42='-msse4.2'. No luck. *Operating System* -bash-3.00$ uname -a SunOS sunfire2 5.10 Generic sun4u sparc SUNW,Sun-Fire-V210 *configure command output. Please let me know if you need config.log file* -checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... yes checking whether NLS is wanted... no checking for default port number... 5432 checking for block size... 8kB checking for segment size... 1GB checking for WAL block size... 8kB checking for WAL segment size... 16MB checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... no checking whether /opt/SUNWspro/bin/cc accepts -g... yes checking for /opt/SUNWspro/bin/cc option to accept ISO C89... none needed checking whether the C compiler still works... yes checking how to run the C preprocessor... /opt/SUNWspro/bin/cc -Xa -E checking allow thread-safe client libraries... yes checking whether to build with Tcl... no checking whether to build Perl modules... no checking whether to build Python modules... no checking whether to build with GSSAPI support... no checking whether to build with PAM support... no checking whether to build with LDAP support... no checking whether to build with Bonjour support... no checking whether to build with OpenSSL support... no checking whether to build with SELinux support... no checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for non-GNU ld... /usr/ucb/ld checking if the linker (/usr/ucb/ld) is GNU ld... no checking for ranlib... ranlib checking for strip... strip checking whether it is possible to strip libraries... no checking for ar... ar checking for a BSD-compatible install... config/install-sh -c checking for tar... /usr/bin/tar checking whether ln -s works... yes checking for gawk... no checking for mawk... no checking for nawk... nawk checking for a thread-safe mkdir -p... /opt/sfw/bin/gmkdir -p checking for bison... /usr/sfw/bin/bison configure: using bison (GNU Bison) 1.875 checking for flex... configure: WARNING: *** The installed version of Flex, /usr/sfw/bin/flex, is too old to use with PostgreSQL. *** Flex version 2.5.31 or later is required, but this is /usr/sfw/bin/flex version 2.5.4. no configure: WARNING: *** Without Flex you will not be able to build PostgreSQL from Git nor *** change any of the scanner definition files. You can obtain Flex from *** a GNU mirror site. (If you are using the official distribution of *** PostgreSQL then you do not need to worry about this because the Flex *** output is pre-generated.) checking for perl... /usr/bin/perl configure: using perl 5.8.4 checking for main in -lm... yes checking for library containing setproctitle... no checking for library containing dlopen... none required checking for library containing socket... -lsocket checking for library containing shl_load... no checking for library containing getopt_long... none required checking for library containing crypt... none required checking for library containing shm_open... -lrt checking for library containing shm_unlink... none required checking for library containing fdatasync... none required checking for library containing sched_yield... none required checking for library containing gethostbyname_r... -lnsl checking for library containing shmget... none required checking for inflate in -lz... yes checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/sta
Re: [GENERAL] Connections - Postgres 9.2
On Tue, May 17, 2016 at 6:28 AM, Lucas Possamai wrote: > hmm.. thanks for all the answers guys... > > > One more question: Those IDLE connections.. are using the server's > resources? > To solve that problem I would need a Pool connection, right? > > Would the pool connection solve that IDLE connections? But more important > than that, are the IDLE connections using the machine's resources ? > Yes. There is no straight way to terminate the IDLE connections from the database end. You would need a connection pooler like pgBouncer which can help you terminate IDLE connections after a certain time. It would be much better if your Application can terminate connections to the database. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Streaming replication, master recycling
On Sat, May 14, 2016 at 5:38 PM, Venkata Balaji N wrote: > > On Wed, May 11, 2016 at 9:04 PM, wrote: > >> I apologise for the missing data. >> >> we are running 9.1.15 on debian servers. >> > > There is a possibility of making the old master standby if you have > promoted standby after clean-shutting down the master. I I tested this in > 9.2.x and later versions. This is a manual process in 9.2, i think it is > the same in 9.1.x version as well. > > The process is - > > - Ensure master and standby are in sync before switching over > - Promote standby > - build recovery.conf at old master with the parameter > recovery_target_timeline set to 'latest' > - When you start the old master, it will ask for timeline history files > which you need to manually transfer from new master > - The old master must become standby > > Hope this helps and works in 9.1.x > I have tested this out and this procedure works in 9.1.x version too. So, you will need to shutdown the master first to ensure roles are successfully reversed. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] tx canceled on standby despite infinite max_standby_streaming_delay
On Sat, May 14, 2016 at 12:27 PM, Jay Howard wrote: > I'm seeing long-running transactions (pg_dump) canceled on the standby > when there are a lot of inserts happening on the master. This despite my > having set max_standby_streaming_delay to -1 on the standby. > Do you have hot_standby_feedback set to "on" ? What is the parameter max_standby_archive_delay configured to ? This will pause WAL archives from being applied when queries are executed on the standby database. > Why might that happen? > > This is pg 9.3.12. When it happens I see: > > pg_dump: Dumping the contents of table "TABLE" failed: PQgetResult() > failed. > pg_dump: Error message from server: ERROR: canceling statement due to > conflict with recovery > DETAIL: User query might have needed to see row versions that must be > removed. > pg_dump: The command was: COPY public.TABLE (COLUMNS) TO stdout; > I suspect this is due to the clean up by VACUUM on primary. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Streaming replication, master recycling
On Wed, May 11, 2016 at 9:04 PM, wrote: > I apologise for the missing data. > > we are running 9.1.15 on debian servers. > There is a possibility of making the old master standby if you have promoted standby after clean-shutting down the master. I I tested this in 9.2.x and later versions. This is a manual process in 9.2, i think it is the same in 9.1.x version as well. The process is - - Ensure master and standby are in sync before switching over - Promote standby - build recovery.conf at old master with the parameter recovery_target_timeline set to 'latest' - When you start the old master, it will ask for timeline history files which you need to manually transfer from new master - The old master must become standby Hope this helps and works in 9.1.x Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Streaming replication, master recycling
On Wed, May 11, 2016 at 2:31 PM, wrote: > Hi All, > > we are currently using streaming replication on multiple node pairs. We > are seeing some issues, but I am mainly interrested in clarification. > > When a failover occurs, we touch the trigger file, promoting the previous > slave to master. That works perfectly. > > For recycling the previous master, we create a recovery.conf (with > recovery_target_timeline = 'latest') and *try* to start up. If postgresql > starts up, we accept it as a new slave. If it does not, we proceed with a > full basebackup. > Which version of postgresql you are using ? You need to shutdown master first, then promote slave and then other way round, but, this can be clarified only if you let us know the postgresql version. This is quite tricky in 9.2.x and from 9.3.x. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Is it possible to recover the schema from the raw files?
On Sun, Apr 24, 2016 at 2:30 AM, Tomas J Stehlik wrote: > Hello, > > There was a corruption to the file system due to sudden shutdown of Windows > 7. > > The attempt to connect to one of the databases results in multiple errors, > like the following one: > ERROR: invalid page in block 58 of relation base/161326/233745 > > Several raw files were completely lost (zeroed), thus utterly disabling the > functionality of this particural database. > > The raw files were backed up separately outside of the original data folder > upon discovery of the issue. > > I would like to ask whether it is possible to recover the database schema > from the raw files? > The data itself is less important in this case. > You will have to restore the data-directory from the backup and perform recovery (if required). This will get you the database schema including the data (or whatever is part of the backup). If you are not worried about the data, then, you will need to manually rebuild the database schema, recovery process will not help you much here. If you cannot re-build the schema manually (meaning: if you do not have scripts available to build the schema), then you will need to restore the database from the backup and manually cleanup all the data, which is quite a bit of work. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Shipping big WAL archives to hot standby
> What would be the effect of suddenly introducing a 1-2 GB of WAL archives > to the WAL restore folder on the slave? Would there be a big performance > effect on the incoming queries to the slave? Would the slave be available > for queries while the WAL logs are restored into the DB? > If the Queries are running on slave whilst WAL archives are being applied, there is a bright chance that, queries would encounter a conflict with the recovery and would eventually get cancelled. You can see messages in the PostgreSQL logfiles. The recommended approach would be to apply WALs when queries are being executed on slave. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Live steraming replication setup issue!
On Tue, Feb 23, 2016 at 10:02 AM, Ashish Chauhan wrote:
> Thanks Venkata, I am able to setup replication now. Just wondering when I
> check replication_delay and lag, I am getting negative number, any idea why?
>
>
>
> receive|replay| replication_delay | lag
>
> --+--+---+-
>
> 796/BA9D8000 | 796/BA9D7FF0 | -00:00:01.612415 | -2
>
The WAL records in receive and replay means the same WAL record, please see
below :
postgres=# select pg_xlogfile_name('796/BA9D8000');
pg_xlogfile_name
--
0001079600BA
(1 row)
postgres=# select pg_xlogfile_name('796/BA9D7FF0');
pg_xlogfile_name
--
0001079600BA
(1 row)
That means the replication is continuously streaming and may behind few WAL
records. Do you see the lag all the time ? Did you test if the replication
is working fine ?
You can check that via pg_controldata as well. What does sync_state in
pg_stat_replication say ?
Regards,
Venkata B N
Fujitsu Australia
Re: [GENERAL] Why is my database so big?
On Mon, Feb 22, 2016 at 3:20 PM, Andrew Smith wrote:
> Hello,
>
> I am setting up a proof of concept database to store some historical
> data. Whilst I've used PostgreSQL a bit in the past this is the first time
> I've looked into disk usage due to the amount of data that could
> potentially be stored. I've done a quick test and I'm a little confused as
> to why it is occupying so much space on disk. Here is my table definition:
>
> CREATE TABLE "TestSize"
> (
> "Id" integer NOT NULL,
> "Time" timestamp without time zone NOT NULL,
> "Value" real NOT NULL,
> "Status" smallint NOT NULL,
> PRIMARY KEY ("Id", "Time")
> );
>
> CREATE INDEX test_index ON "TestSize" ("Id");
>
> With a completely empty table the database is 7 MB. After I insert 1
> million records into the table the database is 121 MB. My understanding is
> that each of the fields is sized as follows:
>
> integer - 4 bytes
> timestamp without time zone - 8 bytes
> real - 4 bytes
> smallint - 2 bytes
>
> So for 1 million records, it needs at least 18 million bytes, or ~17 MB to
> store the data. Now I'm sure there is extra space required for managing the
> primary key fields, the index and other misc overhead involved in getting
> this data into the internal storage format used by PostgreSQL. But even if
> I triple the number of bytes stored for each record, I only end up with 51
> MB or so. Am I missing something obvious?
>
PostgreSQL version please ?
What is the output of below query ?
=# select pg_size_pretty(pg_relation_size('TestSize'));
Do you see any pg_toast tables in the database ? How are you calculating
the database size ?
Each field size could vary depending upon the number of characters you
entered. If you wish to understand the table size stats and its tuple size
"pgstattuple" contrib module is the way to go.
There are other components in the postgresql's data-directory which occupy
the disk space - postgresql logs (if enabled), pg_xlog etc..
What is size of each directory in the data-directory on the disk ?
Regards,
Venkata B N
Fujitsu Australia
Re: [GENERAL] Live steraming replication setup issue!
On Fri, Feb 19, 2016 at 6:24 PM, Ashish Chauhan wrote: > Below is recovery.conf on slave > > > #--- > # STANDBY SERVER PARAMETERS > > #--- > # > # standby_mode > # > # When standby_mode is enabled, the PostgreSQL server will work as a > # standby. It will continuously wait for the additional XLOG records, using > # restore_command and/or primary_conninfo. > # > standby_mode = 'on' > # > # primary_conninfo > # > # If set, the PostgreSQL server will try to connect to the primary using > this > # connection string and receive XLOG records continuously. > # > primary_conninfo = 'host= port=5432' > # > # > # By default, a standby server keeps restoring XLOG records from the > # primary indefinitely. If you want to stop the standby mode, finish > recovery > # and open the system in read/write mode, specify path to a trigger file. > # The server will poll the trigger file path periodically and start as a > # primary server when it's found. > # > trigger_file = '/data/main/primary.trigger' > Can you consider putting recovery_target_timeline='latest' as well ? and can you help us know if you can see anything weird in the postgresql logfiles @ DR ? Is DR in complete sync with the slave ? Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Live steraming replication setup issue!
> > How do I setup replication between DR server and slave server while slave > server and master server are running? I cannot stop master server. Can > someone please guide with steps? > > > Steps are pretty much similar. You can setup replication between slave and DR by using the backup of Master database + WAL archives (if available) and setup primary_conninfo to point to slave database in recovery.conf on DR. Can you please let us know which version of postgresql you are using ? Thats important for us to make any suggestions. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Multiple databases and shared_buffers
On Thu, Feb 18, 2016 at 1:54 AM, Data Cruncher wrote: > We will be creating multiple databases in a cluster (instance). Is there > any way to separate shared_buffers for each database? Looks like not since > PG does not allow user created shared buffers. shared_buffers parameter is for the whole PostgreSQL cluster shared by all the databases in the cluster. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] pg_restore encounter deadlock since PostgreSQL bringing up
On Tue, Nov 17, 2015 at 3:24 PM, zh1029 wrote: > Hi, > > While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL > bringing up. I encounter pg_restore failure because of deadlock detected. > > postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting > for > AccessExclusiveLock on relation 7 of database 24577 after 1000.070 ms > postgres[2737]: [3-2] STATEMENT: DROP SCHEMA public CASCADE; > postgres[2737]: [4-1] err-1: deadlock detected > postgres[2737]: [4-2] DETAIL: Process 2737 waits for AccessExclusiveLock > on > relation 7 of database 24577; blocked by process 2720. > postgres[2737]: [4-3] Process 2720 waits for AccessShareLock on > relation 33344 of database 24577; blocked by process 2737. > postgres[2737]: [4-4] Process 2737: DROP SCHEMA public CASCADE; > postgres[2737]: [4-5] Process 2720: SELECT sequence_name, > start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = > 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 > THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 > AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = > -9223372036854775807 THEN NULL ELSE min_value END AS min_value, > cache_value, is_cycled FROM zonepreprovisioningrules_id_seq > postgres[2737]: [4-6] HINT: See server log for query details. > postgres[2737]: [4-7] STATEMENT: DROP SCHEMA public CASCADE; > > I suspect competition between process that bringing up PostgreSQL and > process drop schema by pg_restore. So my question is how to guarantee (e.g > by inquiring some parameters from system tables? ) PostgreSQL is totally > start up to accept drop schema via pg_restore? > "pg_ctl -D status" command will let you know if the PostgreSQL cluster is up and running. Are you sure, you do not have any other processes running while pg_restore process is running ? Whats the background of the process 2720 ? Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] postgres sometimes returns no data
On Fri, Nov 13, 2015 at 6:49 AM, db042190 wrote: > Hi. We have a postgres 9.1 query in a pentaho job (table input component > contains the query and is followed by various switches) that runs nightly. > More and more frequently (about half the time now), the query returns no > data (or appears to return no data, see last paragraph) when there is > clearly data to be extracted. And pentaho continues as if nothing is > wrong. > Whenever I simply rerun the component manually, the query returns data and > I > am able to complete the job. > > I'm looking at what I think is a postgres log around one of the times this > last happened. And trying to correlate it to the time I see in the pentaho > job's log when this component starts up. > > Around that time I just see a bunch of "checkpoints are occurring too > frequently...". About a minute later and coincidentally around the time > the > pentaho job ends, I see "unexpected eof...could not receive data..target > machine actively refused it.". The reason this last item interests me is > that this component is the last postgres item to run in the job. And the > next/last component wouldn't run long when there is an empty file created > by > the problematic component. The file is empty whenever this behavior > occurs. > When checkpoints are occurring too frequently, then, it is important that you tune the checkpoint parameters to ensure optimal checkpoint behavior. Do you see any IO spike on the postgres server ? As the above message says "could not receive data" ... I suspect, the query is returning the rows at the postgres end without any issues. Did you execute the query on the Postgres database and see if that is returning rows ? if yes, then it is not a query related issue, it has something to do with the performance. Do you notice any other activities on the database when this query is running which are possibly generating High CPU / High IO which can block or abort the application connections ? I'm torn not knowing if this is a postgres issue or pentaho issue. The fact > that the pentaho log doesn't show the switches as having "finished > processing" makes me think either pentaho wouldnt execute those switches > when there is no data or pentaho is the problem. The fact that its > happening more often makes me think the db is somehow involved in the > problem. > > I did a reindex and vacuum recently hoping it would alleviate the problem. > Not so. Any advice would be appreciated. > The first thing to do is to tune the checkpoint parameters. Regards, Venkata B N Fujitsu Australia
[GENERAL] Advise on memory usage limitation by PostgreSQL on Windows
Hello, We would like to know if there is any limitation around memory utilization by PostgreSQL on Windows systems. As of now, we do not have the details about the exact Windows version in use. Windows server is with 5 GB RAM and 4 CPUs (4 cores). PostgreSQL Version is 9.1.x which will possibly be upgraded to 9.4.x. After a detailed analysis on memory usage by OS and other processes, is it safe to advise on configuring shared_buffers to 2 GB ? Any advise will be appreciated. Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] Import Problem
On Thu, Sep 17, 2015 at 1:27 AM, Ramesh T wrote: > Hi All, >I'm using or2pg tool ,I exported data but I have to change the > schema import to postgres database.exported data more than gb. > Can you please let me know, how to do the change the name in data script..? > Do you mean that, you want import the data into a different schema in postgres ? Where do you want to change the name ? If i understand your question correctly, there is an schema option in ora2pg.conf which might help you. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] avoid lock conflict between SELECT and TRUNCATE
On Thu, Sep 10, 2015 at 3:54 AM, Florin Andrei
wrote:
> Once in a while, I have a report running a complex query such as this:
>
> BEGIN;declare "SQL_CUR04919850" cursor with hold for SELECT
> "auths_with_trans"."user_id" AS "user_id (auths_with_trans)",
> MAX("auths_with_trans"."user_created") AS
> "TEMP(attr:user_created:ok)(2099950671)(0)",
> MIN("auths_with_trans"."user_created") AS
> "TEMP(attr:user_created:ok)(99676510)(0)",
> MIN("auths_with_trans"."trans_time") AS
> "usr:Calculation_6930907163324031:ok",
> MIN("auths_with_trans"."auth_created") AS
> "usr:Calculation_9410907163052141:ok"
> FROM "public"."users" "users"
> LEFT JOIN "public"."auths_with_trans" "auths_with_trans" ON
> ("users"."user_id" = "auths_with_trans"."user_id")
> GROUP BY 1;fetch 100 in "SQL_CUR04919850"
>
> But it takes a long time to complete, and meanwhile a cron job tries to
> rebuild the users table by first doing "TRUNCATE TABLE users" and then
> repopulating it with data. Obviously, TRUNCATE is blocked until the long
> SELECT finishes.
>
> I'm looking for ways to avoid the conflict. One way would be to do
> incremental updates to the users table - that's not an option yet.
>
> What if I rename the users table to users_MMDD? Would that still be
> blocked by SELECT? If it's not blocked, then I could rename users out of
> the way, and then recreate it with fresh data as plain 'users'. Then I'd
> have a cron job dropping old users tables when they get too old.
>
>
Yes. Renaming the table would interrupt the ongoing SELECT. The best
approach would be (if possible) to isolate the SELECT itself. You can
consider renaming the "users" table before the SELECT starts (say renamed
to users_orig) and then execute the SELECT on user_orig table and drop it
(if the data is no more needed) after the SELECT finishes. Instead of
TRUNCATE, you can consider re-creating the "users" table and populating the
data. If you take this approach, you will need to be careful regarding
privileges/grants and dependencies on the table.
Or the second approach would be --
Create a table called users_orig from the "users" table and execute SELECT
on user_orig table and let the TRUNCATE/data-repopulation operation run on
"users" table. This will be a problem if the data is huge. It might take up
your hardware resources.
Third and simple approach would be to -
Execute SELECT and TRUNCATE at different times.
All of the above approaches are without considering data-size and other
critical aspects of environment, which you need to worry about.
Regards,
Venkata Balaji N
Fujitsu Australia
Re: [GENERAL] Disconnected but query still running
On Sat, Jul 11, 2015 at 8:53 AM, Eduardo Piombino wrote: > > I've read that that a query still running after a disconnection is > normal, since the server won't realize about this disconnection until > it needs to send some data back to the client, which is ok and > understandable. But my question is, what happens when the query > finishes? FYI, the query was an update, in what would be autocommit > mode (no explicit transaction was open). Will the changes be commited? > or rollbacked because it will detect the disconnection later on? If the autocommit is on and the transaction is not with-in BEING-END block, then, the changes should be committed. If the query/process detects the disconnection later on, it simply hangs which needs to be killed manually at the database end. > In > other words, is it worth waiting for this query to finish (it has been > running for almost 7 hours now), or should I cancel it because it will > irremediably be rolled back when the server finds theres no one on the > other end of the tcp connection? > Generally, when the application session is killed abnormally, the query still continues at the database end. If you want process not to continue anymore at the database-end, then, you need to manually kill it at the database end too using pg_cancel_backend() or pg_terminate_backend(). Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] pg_xlog on a hot_stanby slave
On Tue, Jun 16, 2015 at 6:55 PM, Xavier 12 wrote: > Hi everyone, > > Questions about pg_xlogs again... > I have two Postgresql 9.1 servers in a master/slave stream replication > (hot_standby). > > Psql01 (master) is backuped with Barman and pg_xlogs is correctly > purged (archive_command is used). > > Hower, Psql02 (slave) has a huge pg_xlog (951 files, 15G for 7 days > only, it keeps growing up until disk space is full). I have found > documentation and tutorials, mailing list, but I don't know what is > suitable for a Slave. Leads I've found : > > - checkpoints > - archive_command > - archive_cleanup > > Master postgresq.conf : > > [...] > wal_level = 'hot_standby' > archive_mode = on > archive_command = 'rsync -az /var/lib/postgresql/9.1/main/pg_xlog/%f > bar...@nas.lan:/data/pgbarman/psql01/incoming/%f' > max_wal_senders = 5 > wal_keep_segments = 64 > autovacuum = on > > Slave postgresql.conf : > > [...] > wal_level = minimal > wal_keep_segments = 32 > hot_standby = on > > Slave recovery.conf : > > standby_mode = 'on' > primary_conninfo = 'host=10.0.0.1 port=5400 user=postgres' > trigger_file = '/var/lib/postgresql/9.1/triggersql' > restore_command='cp /var/lib/postgresql/9.1/wal_archive/%f "%p"' > archive_cleanup_command = > '/usr/lib/postgresql/9.1/bin/pg_archivecleanup > /var/lib/postgresql/9.1/wal_archive/ %r' > > > How can I reduce the number of WAL files on the hot_stanby slave ? > If the number of WAL files in pg_xlog are growing, then you need to look at why the files are not getting deleted. Do you see master and standby in sync ? You can check that by getting the current pg_xlog position in standby. Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] Missing WALs when doing pg_basebackup from slave...
On Wed, Jun 10, 2015 at 6:47 PM, wrote: Is it normal that pg_basebackup runs successfully (rc=0) and there is no > WAL files present? > Yes, it is normal. "pg_basebackup" ensures that required WALs are backed along with the data directory. This is to ensure backup is consistent. > The master and slave are sitting idle, after only a few transaction on the > master at the beginning of the day. I noted that all WAL switches are > caused by the backup running on the master. Is it possible the slave is in > a consistent state when it has applied all changes from the previous WAL > and the new WAL hasn't been created yet on the master (so actually no WAL-s > are needed to restore it to a consistent state)? > I am not sure if I got your question correct. The amount of transactions in the master database may be low or high, the WALs will be replicated to slave. To ensure slave is receiving all the WALs, you need to check the sync status between master and slave. If there is no new WAL generated at master, then slave must be in consistent state should have applied all the previous WALs, if not, then all the previous WALs are needed to get the slave to a consistent state. Nothing can be advised straight without knowing your replication configuration/architecture details. Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] Queries for unused/useless indexes
On Fri, May 22, 2015 at 11:41 PM, Melvin Davidson wrote: > > Over the years I've wrote many scripts and queries to track the database > status. Recently I've had to convince a client who thought it was a good > idea to create indexes for every column on every table that it is really a > bad idea. To do so, I wrote useless_indexes2.sql, which shows every index > that has never been scanned. They still didn't believe me, so I wrote > wasted_index_space.sql. That shows how much space is wasted by all the > unused indexes. > > I'd like to share those queries with the community, as I know there must > be others out there with the same problem. > I had a similar problem a few times in the past with some of our clients. I completely agree that it is not at all a good idea and we are simply inviting an extra over-head from maintenance and performance perspective. Indexing every column of the table does not make sense as it is almost impossible that every column of the table can have rows with high cardinality. That's not typical RDBMS design. Generally, most of them believe that, if an Index is unused, though its not beneficial at-least its not a harm. That is not correct. - Depending on the data-types and cardinality of the columns, Indexes can occupy a lot of space and remain unused. This invites maintenance over-head (ex: backups and vacuum operations) - The biggest problem is, if the table is a heavy-write table, even though Indexes are not picked during SELECT, they cannot escape WRITES (INSERTS/UPDATES). This is purely an extra and unnecessary I/O. /* useless_indexes.sql */ > SELECT >idstat.schemaname AS schema, >idstat.relname AS table_name, >indexrelname AS index_name, >idstat.idx_scan AS times_used, >pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || > '.' || quote_ident(idstat.relname))) AS table_size, >pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || > '.' || quote_ident(indexrelname))) AS index_size, >n_tup_upd + n_tup_ins + n_tup_del as num_writes, >indexdef AS definition > FROM pg_stat_user_indexes AS idstat > JOIN pg_indexes ON indexrelname = indexname > JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname > WHERE idstat.idx_scan < 200 > AND indexdef !~* 'unique' > ORDER BY idstat.schemaname, > idstat.relname, > indexrelname; > Not sure why do you have "<200" Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master
On Fri, May 22, 2015 at 6:52 AM, Piotr Gasidło wrote: > Got strange problem. Unable to repeat, but got logs. > > Simple master-slave using streaming replication. > Master is running. Slave is down. > Segment 00044C4D0090 was successfully archived and send > from master to slave. > > Now I've started slave, and: > > ay 21 21:23:37 d8 postgres[50645]: [3-1] 2015-05-21 21:23:37.033 CEST > @ 50645 LOG: database system was shut down in recovery at > 2015-05-21 21:22:03 CEST > May 21 21:23:37 d8 postgres[50645]: [4-1] 2015-05-21 21:23:37.034 CEST > @ 50645 LOG: entering standby mode > May 21 21:23:37 d8 postgres[50645]: [5-1] 2015-05-21 21:23:37.058 CEST > @ 50645 LOG: restored log file "00044C4D0088" from > archive > May 21 21:23:37 d8 postgres[50645]: [6-1] 2015-05-21 21:23:37.120 CEST > @ 50645 LOG: redo starts at 4C4D/88493B50 > May 21 21:23:37 d8 postgres[50645]: [7-1] 2015-05-21 21:23:37.226 CEST > @ 50645 LOG: restored log file "00044C4D0089" from > archive > May 21 21:23:37 d8 postgres[50645]: [8-1] 2015-05-21 21:23:37.426 CEST > @ 50645 LOG: restored log file "00044C4D008A" from > archive > May 21 21:23:37 d8 postgres[50645]: [9-1] 2015-05-21 21:23:37.750 CEST > @ 50645 LOG: restored log file "00044C4D008B" from > archive > May 21 21:23:38 d8 postgres[50645]: [10-1] 2015-05-21 21:23:38.376 > CEST @ 50645 LOG: restored log file "00044C4D008C" from > archive > May 21 21:23:38 d8 postgres[50645]: [11-1] 2015-05-21 21:23:38.690 > CEST @ 50645 LOG: restored log file "00044C4D008D" from > archive > May 21 21:23:38 d8 postgres[50645]: [12-1] 2015-05-21 21:23:38.855 > CEST @ 50645 LOG: restored log file "00044C4D008E" from > archive > May 21 21:23:39 d8 postgres[50645]: [13-1] 2015-05-21 21:23:39.275 > CEST @ 50645 LOG: restored log file "00044C4D008F" from > archive > May 21 21:23:39 d8 postgres[50645]: [14-1] 2015-05-21 21:23:39.654 > CEST @ 50645 LOG: restored log file "00044C4D0090" from > archive > May 21 21:23:40 d8 postgres[50645]: [15-1] 2015-05-21 21:23:40.222 > CEST @ 50645 LOG: consistent recovery state reached at > 4C4D/90FFF9C8 > May 21 21:23:40 d8 postgres[50644]: [3-1] 2015-05-21 21:23:40.222 CEST > @ 50644 LOG: database system is ready to accept read only > connections > May 21 21:23:40 d8 postgres[50645]: [16-1] 2015-05-21 21:23:40.223 > CEST @ 50645 LOG: unexpected pageaddr 4C46/E00 in log segment > 00044C4D0091, offset 0 > May 21 21:23:40 d8 postgres[50699]: [4-1] 2015-05-21 21:23:40.232 CEST > @ 50699 LOG: started streaming WAL from primary at 4C4D/9000 on > timeline 4 > May 21 21:23:40 d8 postgres[50699]: [5-1] 2015-05-21 21:23:40.232 CEST > @ 50699 FATAL: could not receive data from WAL stream: ERROR: > requested WAL segment 00044C4D0090 has already been > removed > May 21 21:23:40 d8 postgres[50645]: [17-1] 2015-05-21 21:23:40.255 > CEST @ 50645 LOG: restored log file "00044C4D0090" from > archive > May 21 21:23:40 d8 postgres[50703]: [4-1] 2015-05-21 21:23:40.268 CEST > @ 50703 LOG: started streaming WAL from primary at 4C4D/9000 on > timeline 4 > May 21 21:23:40 d8 postgres[50703]: [5-1] 2015-05-21 21:23:40.268 CEST > @ 50703 FATAL: could not receive data from WAL stream: ERROR: > requested WAL segment 00044C4D0090 has already been > removed > May 21 21:23:40 d8 postgres[50703]: [5-2] > ... > (and so on) > > So, as I understand: > - slave was started and entered restore, > - slave restored 00044C4D0090 from archive, reached > consistent recovery state > - now, it connected to master and noticed, that, it has > 00044C4D0091 segment uncomplete > - and then, started yelling about missing segment (master deleted it > already after archiving) on master > > Why? It might be yelling about the WAL segment due to the delay in shipping it from master to slave. Do you have the restore_command set up in the recovery.conf file ? do you have any automated job which is shipping WAL archives from master to slave. Slave - if it does not find the WAL segment in master pg_xlog location, it looks for the same in the location mentioned in the restore_command. It yells if it is unable to find it. Cannot give any concrete comments/suggestions, until recovery.conf entries are known. > I fixed it by stopping slave, doing some operations on master, copying > 00044C4D0091 and 00044C4D0092 from master, and > staring the slave: > > > May 21 21:24:56 d8 postgres[50644]: [4-1] 2015-05-21 21:24:56.160 CEST > @ 50644 LOG: received fast shutdown request > May 21 21:24:56 d8 postgres[50644]: [5-1] 2015-05-21 21:24:56.160 CEST > @ 50644 LOG: aborting any active transactions > May 21 21:24:56 d8 postgres[50657]: [3-1] 2015-05-21 21:24:56.162 CEST > @ 50657 LOG: shutting down > May 21 21:24:56 d8 postgres[50657]: [4-1] 2015-
Re: [GENERAL] pg_xlog Concern
On Wed, May 20, 2015 at 4:12 PM, Sachin Srivastava wrote: > Dear Venkata, > > I have not set this parameter "archive_timeout" > > I think that is also the reason. > > What value I'll assign to this parameter if my *Checkpoint_segments= 200 *and > *checkpoint_timeout= 5min, *kindly confirm. > > Before we get on to that, can you please let us know what is archive_status directory saying. Do you see any files there ? if yes, what is there extension ? We need to first investigate why pg_xlog is growing, is it because of long running transactions or archiving is not working as expected. It is the checkpoint process which cleans up the WAL files from pg_xlog, if that is not happening Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] pg_xlog Concern
On Mon, May 18, 2015 at 9:44 PM, Sachin Srivastava wrote: > Hi, > > > > I have done below changes in my postgresql.conf. > > > > *Checkpoint_segments= 200* > > and > > *checkpoint_timeout= 5min* > > > > I am also doing archiving and below changes in my postgresql.conf. > > > > *Wal_level=archive* > > *archive_command= cp %p /dbkup/momacpp_213_live/%f* > > *archive_mode=on* > > > > Achieving is going smoothly in /*dbkup/momacpp_213_live* folder. > > > > > > But currently my pg_xlog size is 60 GB and there are 3740 WAL file in this > folder and in Last week this was 400 GB(pg_xlog folder) and WAL file were > approx. 3. Due to archiving pg_xlog folder size is decreasing now but > it’s taking one week to come in normal size. > > > > > > *I have 2 Question*: > > > > *First:* When I have given *checkpoint segments=200 (As per my knowledge > WAL file should be generated 200*2+3=403 only)* but why it’s generating > too much file. MY each WAL file is 16 MB. > What do you see in the "archive_status" directory ? Other way around could be, if you have log_checkpoints parameter set to "on", then checkpoints information will be logged into the postgresql logfile. This will help you understand how checkpoints are behaving. > *Second:* Why pg_xlog size is increasing too much it should be only > (403*16 MB = 6448 MB) and if Production team is not entering data in bulk > and if normal production is going then it’s size remain same as per logic. > Do you mean to say, pg_xlog is not getting cleared forever ? or is it getting cleared periodically and you are uncertain about the behaviour ? > How I handle this case (pg_xlog folder size) when Production people > entering the data in bulk, kindly suggest. I am missing something in my > postgresql.conf and somewhere else. > What is the *archive_timeout* value you have ? Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] Standby problem after restore_command Implementation
On Tue, May 5, 2015 at 7:57 AM, Edson F. Lidorio wrote: > > > On 04-05-2015 00:46, Venkata Balaji N wrote: > >> You do not see the above WAL file in the archive directory ? >> "/mnt/server/archivedir" is shared between master and slave databases ? The >> file may have been removed from the archive location. >> > Strange !! > Yes, this specified folder file "/ mnt / server / ArchiveDir". > At this shared folder between master and slave. > I suspect that archive cleanup job is cleaning up the archives. "archive_cleanup_command" removes any archives not needed for recovery. Since the the archive destination is shared across master and slave. Archives are removed and Standby is not getting the required archives. To avoid this, before you do any tests, i suggest remove "archive_cleanup_command" parameter and see if you are still facing the same problem. Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] How to keep pg_largeobject from growing endlessly
> I'm routinely vacuumlo'ing to reap orphan OIDs. Is it necessary to > manually vacuum pg_largobject or is it handled by autovacuum? > It is handled by autovacuum. What we do is, we schedule a manual VACUUM ANALYZE nightly job on bigger tables to avoid burden on the autovacuum during the business time. In a system where large objects are constantly added (and *some* rarely > deleted, so it grows every day), would I gain space (freed to the OS) by > VACUUM FULL it? > The amount of reclaimed space will depend on the volume of deletions happening. If the DELETES are rare and are not deleting much, then frequent VACUUM FULL is not ideal. Regards, Venkata Balaji N Fujitsu Australia
Re: [GENERAL] Where does vacuum FULL write temp-files?
> I'm planning to vacuum FULL a pg_largeobject relation (after vacuumlo'ing > it). The relation is 300GB large so I'm concerned the operation will write > full my pg_xlog directory which is on a 200GB (net) RAID1 SSD. > > Where does vacuum FULL rewrite to, does it use pg_xlog or some other > directory? > > Which version of PostgreSQL is this ? If i got your question correctly, VACUUM FULL would rewrite the data to a new data file associated with that particular relation (Table) in the "$PGDATA/base" directory. This needs an extra disk space at the OS level (this is not related to pg_xlog directory). As VACUUMING is a data change operation, "pg_xlog" will also have only the WAL data (modifications) written at the time of VACUUMING. http://www.postgresql.org/docs/9.4/static/sql-vacuum.html Regards, Venkata Balaji N Fujitsu Australia
