Re: [GENERAL] What is the 'data2' directory for?

[Tom Lane]

Steven Hirsch  writes:
> I notice that PostgreSQL (both 9.5.4 and 9.6) create a 'data2' directory 
> as a peer to the 'data' directory I specified at database initialization. 
> What is this directory for and what parameter determines its location?

Postgres itself certainly does no such thing.  Maybe it's being done in
some wrapper script you're using?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is the 'data2' directory for?

[Bruce Momjian]

On Tue, Oct 25, 2016 at 06:03:43PM -0400, Steven Hirsch wrote:
> I notice that PostgreSQL (both 9.5.4 and 9.6) create a 'data2' directory as
> a peer to the 'data' directory I specified at database initialization. What
> is this directory for and what parameter determines its location?

I am guessing someone at your site created it --- Postgres does not
create such directories by default.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What is the 'data2' directory for?

[Steven Hirsch]

I notice that PostgreSQL (both 9.5.4 and 9.6) create a 'data2' directory 
as a peer to the 'data' directory I specified at database initialization. 
What is this directory for and what parameter determines its location?



--


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Errors while installing PostGIS by an unusual method

[John R Pierce]

On 10/24/2016 11:08 AM, Ankit Sablok wrote:
Thanks for replying Tom and Asif, I think the issue was that libpq.so 
present in the /pgsql/lib directory did not have all its dependencies 
installed on the system namely openssl hence I execute the command 
sudo yum install openssl and that proceeded the configure script 
forward, but now I ran into a separate issue where I do see libxml2 
libraries present in the /pgsql/lib directory and I have also set the 
appropriate PATH and LD_LIBRARY_PATH for the build to happen 
successfully, here is the configure log when I execute the following 
command


you typically need to install the corresponding -devel packages if 
you're building software with those libraries, like libxml2-devel, 
openssl-devel, etcetc.



--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Errors while installing PostGIS by an unusual method

[Daniel Caldeweyher]

Hi Ankit,

You might have specific requirements to need PG 9.6. But if 9.5 is
sufficient, it will be easier to simply do a rpm/yum install.

Regards,
Daniel

On Tue, Oct 25, 2016 at 4:08 AM, Ankit Sablok  wrote:

> Thanks for replying Tom and Asif, I think the issue was that libpq.so
> present in the /pgsql/lib directory did not have all its dependencies
> installed on the system namely openssl hence I execute the command sudo yum
> install openssl and that proceeded the configure script forward, but now I
> ran into a separate issue where I do see libxml2 libraries present in the
> /pgsql/lib directory and I have also set the appropriate PATH and
> LD_LIBRARY_PATH for the build to happen successfully, here is the configure
> log when I execute the following command
>
> ./configure --with-gdalconfig=/pgsql/bin/gdal-config
> --with-geosconfig=//pgsql/bin/geos-config --with-projdir=/pgsql 
> --with-jsondir=/rdsdbbin/apg-1.0.6.2
> --with-pgconfig=/pgsql/bin/pg_config --with-libxml2=/pgsql/bin/xml2-config
>
> *configure: WARNING: unrecognized options: --with-libxml2*
> *checking build system type... x86_64-unknown-linux-gnu*
> *checking host system type... x86_64-unknown-linux-gnu*
> *checking how to print strings... printf*
> *checking for gcc... gcc*
> *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... yes*
> *checking whether gcc accepts -g... yes*
> *checking for gcc option to accept ISO C89... none needed*
> *checking for a sed that does not truncate output... /bin/sed*
> *checking for grep that handles long lines and -e... /bin/grep*
> *checking for egrep... /bin/grep -E*
> *checking for fgrep... /bin/grep -F*
> *checking for ld used by gcc... /usr/bin/ld*
> *checking if the linker (/usr/bin/ld) is GNU ld... yes*
> *checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm*
> *checking the name lister (/usr/bin/nm) interface... BSD nm*
> *checking whether ln -s works... yes*
> *checking the maximum length of command line arguments... 1572864*
> *checking whether the shell understands some XSI constructs... yes*
> *checking whether the shell understands "+="... yes*
> *checking how to convert x86_64-unknown-linux-gnu file names to
> x86_64-unknown-linux-gnu format... func_convert_file_noop*
> *checking how to convert x86_64-unknown-linux-gnu file names to toolchain
> format... func_convert_file_noop*
> *checking for /usr/bin/ld option to reload object files... -r*
> *checking for objdump... objdump*
> *checking how to recognize dependent libraries... pass_all*
> *checking for dlltool... no*
> *checking how to associate runtime and link libraries... printf %s\n*
> *checking for ar... ar*
> *checking for archiver @FILE support... @*
> *checking for strip... strip*
> *checking for ranlib... ranlib*
> *checking for gawk... gawk*
> *checking command to parse /usr/bin/nm output from gcc object... ok*
> *checking for sysroot... no*
> *checking for mt... no*
> *checking if : is a manifest tool... no*
> *checking how to run the C preprocessor... gcc -E*
> *checking for ANSI C header files... yes*
> *checking for sys/types.h... yes*
> *checking for sys/stat.h... yes*
> *checking for stdlib.h... yes*
> *checking for string.h... yes*
> *checking for memory.h... yes*
> *checking for strings.h... yes*
> *checking for inttypes.h... yes*
> *checking for stdint.h... yes*
> *checking for unistd.h... yes*
> *checking for dlfcn.h... yes*
> *checking for objdir... .libs*
> *checking if gcc supports -fno-rtti -fno-exceptions... no*
> *checking for gcc option to produce PIC... -fPIC -DPIC*
> *checking if gcc PIC flag -fPIC -DPIC works... yes*
> *checking if gcc static flag -static works... no*
> *checking if gcc supports -c -o file.o... yes*
> *checking if gcc supports -c -o file.o... (cached) yes*
> *checking whether the gcc linker (/usr/bin/ld -m elf_x86_64) supports
> shared libraries... yes*
> *checking whether -lc should be explicitly linked in... no*
> *checking dynamic linker characteristics... GNU/Linux ld.so*
> *checking how to hardcode library paths into programs... immediate*
> *checking whether stripping libraries is possible... yes*
> *checking if libtool supports shared libraries... yes*
> *checking whether to build shared libraries... yes*
> *checking whether to build static libraries... yes*
> *checking for gcc... (cached) gcc*
> *checking whether we are using the GNU C compiler... (cached) yes*
> *checking whether gcc accepts -g... (cached) yes*
> *checking for gcc option to accept ISO C89... (cached) none needed*
> *checking how to run the C preprocessor... gcc -E*
> *checking for grep that handles long lines and -e... (cached) /bin/grep*
> *checking for ant... no*
> *checking for cpp... /usr/local/bin/cpp*
> *checking if gcc 

Re: [GENERAL] streaming replication and WAL

[t.dalpo...@gmail.com]

Sure you're right... my oversight, sorry. I wanted only to create a 
situation in which the standby remains quite behind with updates, so we 
can suppose that there is a list of standby servers (so the primary 
keeps going on with the 2nd) or simply suppose the replication async.

Pupillo

Il 25/10/2016 18:17, Francisco Olarte ha scritto:

I may be confused but...

On Tue, Oct 25, 2016 at 5:08 PM, t.dalpo...@gmail.com
 wrote:

These servers are configured for Sync streaming replication .
Let's suppose that the standby stays down for a long time, then it restarts,

Doesn't sync replication plus standby down mean primary will stop
accepting work?

Francisco Olarte.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] streaming replication and WAL

[Francisco Olarte]

I may be confused but...

On Tue, Oct 25, 2016 at 5:08 PM, t.dalpo...@gmail.com
 wrote:
> These servers are configured for Sync streaming replication .
> Let's suppose that the standby stays down for a long time, then it restarts,

Doesn't sync replication plus standby down mean primary will stop
accepting work?

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] update records to have occurance number

[Cachique]

HI:

You can use windows functions. https://www.postgresql.org/
docs/9.5/static/tutorial-window.html
specifically row_number(). https://www.postgresql.org/
docs/9.5/static/tutorial-window.html

I'm assuming that your ordering is based on lname and then fname.

The query for getting 'number' is

sandbox=# select row_number() over (partition by lname order by fname,
lname) number, fname, lname from person
 number | fname  | lname
++---
  1 | first  | last
  2 | second | last
  3 | third  | last
  1 | first  | other
  2 | next   | other
(5 rows)

And combined with UPDATE FROM... https://www.postgresql.org/
docs/9.5/static/sql-update.html

sandbox=# update person p set number = d.number from (select row_number()
over (partition by lname order by fname, lname) number, fname, lname from
person) d where p.fname = d.fname and p.lname = d.lname;
UPDATE 5


Regards,
Walte

On Tue, Oct 25, 2016 at 12:06 PM, Mark Lybarger  wrote:

> I want to update a table to have the value of the occurrence number.  For
> instance, I have the below table.  I want to update the number column to
> increment the count of last name occurrences, so that it looks like this:
>
> first last 1
> second last 2
> third last 3
> first other 1
> next other 2
>
> Here's my simple table:
>
> create table person ( fname text, lname text, number integer);
>
> insert into person (fname, lname) values ('first', 'last');
> insert into person (fname, lname) values ('second', 'last');
> insert into person (fname, lname) values ('third', 'last');
>
> insert into person (fname, lname) values ('first', 'other');
> insert into person (fname, lname) values ('next', 'other');
>
> How would I issue an update statement to update the number column?
>
> thanks!
>

Re: [GENERAL] update records to have occurance number

[Alban Hertroys]

> On 25 Oct 2016, at 17:06, Mark Lybarger  wrote:
> 
> I want to update a table to have the value of the occurrence number.  For 
> instance, I have the below table.  I want to update the number column to 
> increment the count of last name occurrences, so that it looks like this:
> 
> first last1
> secondlast2
> third last3
> first other   1
> next  other2
> 
> Here's my simple table:
> 
> create table person ( fname text, lname text, number integer);
> 
> insert into person (fname, lname) values ('first', 'last');
> insert into person (fname, lname) values ('second', 'last');
> insert into person (fname, lname) values ('third', 'last');
> 
> insert into person (fname, lname) values ('first', 'other');
> insert into person (fname, lname) values ('next', 'other');
> 
> How would I issue an update statement to update the number column?

That depends on which order you want the database to perceive those rows in.
The above example suggests that alphabetical order on fname might work, in 
which case:

update person set number = count(p2.fname) +1
  from person p2
 where p2.lname = person.lname
   and p2.fname < person.fname;

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] streaming replication and WAL

[Alan Hodgson]

On Tuesday 25 October 2016 17:08:26 t.dalpo...@gmail.com wrote:
> Hi,
> let's suppose I have:
>   - a primary server with its own local archive location, configured for
> continuous archiving
>   - a standby server without archive.
> These servers are configured for Sync streaming replication .
> Let's suppose that the standby stays down for a long time, then it
> restarts, goes into catchup mode and now needs some old WALs from the
> server archive location.
> Will the standby be able to automatically drain those files through the
> replication or only the WALs being currently updated by the primary ?
> 

It would need its own direct access to the master's archive.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] streaming replication and WAL

[t.dalpo...@gmail.com]

Hi,
let's suppose I have:
 - a primary server with its own local archive location, configured for 
continuous archiving

 - a standby server without archive.
These servers are configured for Sync streaming replication .
Let's suppose that the standby stays down for a long time, then it 
restarts, goes into catchup mode and now needs some old WALs from the 
server archive location.
Will the standby be able to automatically drain those files through the 
replication or only the WALs being currently updated by the primary ?


Regards
Pupillo






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] update records to have occurance number

[Mark Lybarger]

I want to update a table to have the value of the occurrence number.  For
instance, I have the below table.  I want to update the number column to
increment the count of last name occurrences, so that it looks like this:

first last 1
second last 2
third last 3
first other 1
next other 2

Here's my simple table:

create table person ( fname text, lname text, number integer);

insert into person (fname, lname) values ('first', 'last');
insert into person (fname, lname) values ('second', 'last');
insert into person (fname, lname) values ('third', 'last');

insert into person (fname, lname) values ('first', 'other');
insert into person (fname, lname) values ('next', 'other');

How would I issue an update statement to update the number column?

thanks!