Re: using graph model with PostgreSQL

[김세훈]

Thanks guys,
I appreciate your answers.


2018-08-17 1:10 GMT+09:00 Data Ace :

> I think's its a forked PostgreSQL, try AgensGraph:
>
> https://www.postgresql.org/download/products/8/
>
> On Wed, Aug 15, 2018 at 7:09 AM, 김세훈  wrote:
>
>> Hi there,
>>
>> currently I'm using PostgreSQL with PostGIS extension to handle
>> geospatial data.
>>
>> In my project I need to apply some graph algorithms like MST for some
>> network of GPS coordinates.
>>
>> I know there is some way of using Neo4j with PostgreSQL but is there any
>> other way to construct
>>
>> graph model within PostgreSQL environment?
>>
>> Any external modules would be welcomed.
>>
>>
>> Thanks.
>>
>>
>>
>

Re: vPgSql

[Tim Cross]

Vlad ABC  writes:

> On Fri, 2018-08-17 at 15:45 +0300, Dmitry Igrishin wrote:
>> 
>> Looking nice! Thank you. But I unable to start it on Ubuntu, because
>> there is no bash(1) in /usr/bin.
>
> Thank you, i'll fix it.
>

I think pretty much all *nix systems put core shells like sh, bash, zsh
etc in /bin (as it is guaranteed to be available immediately at boot, while 
/usr is
not - it could be a separate partition which isn't available until later
in the boot process).

A way to avoid platform differences is to use /usr/bin/env e.g.

#!/usr/bin/env bash

. 
>> Also, it is open source?
>
> No, it is freeware

oh well, too bad.

-- 
Tim Cross

Re: vPgSql

[Vlad ABC]

On Fri, 2018-08-17 at 15:45 +0300, Dmitry Igrishin wrote:
> 
> Looking nice! Thank you. But I unable to start it on Ubuntu, because
> there is no bash(1) in /usr/bin.

Thank you, i'll fix it.

> Also, it is open source?

No, it is freeware

Re: vPgSql

[Dmitry Igrishin]

пт, 17 авг. 2018 г. в 21:19, Joshua D. Drake :
>
> On 08/17/2018 05:45 AM, Dmitry Igrishin wrote:
> > Hey Vlad
> > пт, 17 авг. 2018 г. в 15:31, Vlad Alexeenkov :
> >> Maybe will be useful for someone
> >>
> >> Very simple Postgres SQL client vPgSql:
> >>
> >> https://vsdev.ru
> > Looking nice! Thank you. But I unable to start it on Ubuntu, because
> > there is no bash(1) in /usr/bin.
>
> Bash is in /bin not /usr/bin. You should be able to edit the first line
> of the start file to fix that.
I know that, thanks :-) I just pointed out the issue to the OP.

Re: vPgSql

[Joshua D. Drake]

On 08/17/2018 05:45 AM, Dmitry Igrishin wrote:

Hey Vlad
пт, 17 авг. 2018 г. в 15:31, Vlad Alexeenkov :

Maybe will be useful for someone

Very simple Postgres SQL client vPgSql:

https://vsdev.ru

Looking nice! Thank you. But I unable to start it on Ubuntu, because
there is no bash(1) in /usr/bin.


Bash is in /bin not /usr/bin. You should be able to edit the first line 
of the start file to fix that.


JD



Also, it is open source?



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

including header files in a C extension

[TalGloz]

Hi,

If I have an external library that I install using make install into the
/usr/local/include/libraryname path by default and I want to include it in a
C extension, the Makefile for the .so file includes the headers in
/usr/local/include/libraryname path. 

Can I use #include  when writing C/CPP code for my
PostgreSQL C extension or do I have to install the external library in a
different path than /usr/local/include/libraryname. I think that the
external library headers are not getting included. 

I do manage to build the .so file without error.

Thanks,
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pg_stat_activity.query_start in the future?

[Adrian Klaver]

On 08/17/2018 09:11 AM, Justin Pryzby wrote:

I'm buried and not having any good ideas how to diagnose this or what else to
send, so here it is.  Feel free to tell me why I'm the one whose confused..

postgres=# SELECT pid, now(), query_start, state, query FROM pg_stat_activity 
WHERE pid=27757;
   pid  |  now  |  query_start  | state 
 |query
---+---+---++-
  27757 | 2018-08-17 11:10:16.568429-04 | 2018-08-17 10:17:52.814282-04 | 
active | autovacuum: VACUUM ANALYZE public.eric_enodeb_cell_20180815
(1 row)

postgres=# \! ps -O lstart 27757
   PID  STARTED S TTY  TIME COMMAND
27757 Fri Aug 17 08:53:20 2018 S ?00:11:56 postgres: autovacuum worker 
process   xton
postgres=# \! date
Fri Aug 17 11:10:58 EDT 2018


Looks to me like the autovacuum process started at ~08:53 and the most 
recent query in that process ran at ~10:17



https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

"query_start 	timestamp with time zone 	Time when the currently active 
query was started, or if state is not active, when the last query was 
started"


Justin




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

pg_stat_activity.query_start in the future?

[Justin Pryzby]

I'm buried and not having any good ideas how to diagnose this or what else to
send, so here it is.  Feel free to tell me why I'm the one whose confused..

postgres=# SELECT pid, now(), query_start, state, query FROM pg_stat_activity 
WHERE pid=27757;
  pid  |  now  |  query_start  | state  
|query
---+---+---++-
 27757 | 2018-08-17 11:10:16.568429-04 | 2018-08-17 10:17:52.814282-04 | active 
| autovacuum: VACUUM ANALYZE public.eric_enodeb_cell_20180815
(1 row)

postgres=# \! ps -O lstart 27757
  PID  STARTED S TTY  TIME COMMAND
27757 Fri Aug 17 08:53:20 2018 S ?00:11:56 postgres: autovacuum worker 
process   xton
postgres=# \! date
Fri Aug 17 11:10:58 EDT 2018

Justin

Re: Difference between "autovacuum_naptime" and "autovacuum_vacuum_cost_delay"?

[Adrian Klaver]

On 08/16/2018 09:42 PM, Raghavendra Rao J S V wrote:

Thank you very much for your response.

Could you clarify me below things,please?


The docs can:

https://www.postgresql.org/docs/10/static/routine-vacuuming.html#AUTOVACUUM

https://www.postgresql.org/docs/10/static/runtime-config-autovacuum.html



What is the difference between "autovacuum_naptime" and 
"autovacuum_vacuum_cost_delay"?


What is the difference between "autovacuum launcher process" and 
"autovacuum worker process"?


How to control the number of "autovacuum launcher process" and 
"autovacuum worker process"?


Does "autovacuum launcher process" sleeps? If yes,which parameter 
controls it?


Does "autovacuum worker process" sleeps? If yes,which parameter controls it?

Regards,
Raghavendra Rao


On 17 August 2018 at 09:30, Joshua D. Drake > wrote:


On 08/16/2018 06:10 PM, Raghavendra Rao J S V wrote:

Hi All,

I have gone through several documents but I am still have
confusion related to "autovacuum_naptime" and
"autovacuum_vacuum_cost_delay". Could you clarify me with an example.

When Auto vacuum worker process will start?



Autovacuum checks for relations that need to be vacuumed/analyzed
every "naptime"


When Auto vacuum worker process will stop?


When it is done with the list of relations that needed work that
were found at the launch of "naptime"



Does Auto vacuum worker process will sleep like Auto vacuum
launcher process ?


The launcher process sleeps for naptime, then wakes up to check what
needs to be worked on



What is the difference between Auto vacuum launcher process and
Auto vacuum worker process?


The launcher is the process that spawns the worker processes (I think).

JD




-- 
Regards,

Raghavendra Rao J S V



-- 
Command Prompt, Inc. ||http://the.postgres.company/  || @cmdpromptinc

***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn:https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *




--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425



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

Error installing postgresql 9.5.14 on windows7: “unable to write inside temp environment variable path”

[abhinav srivastava]

Hi,
I have already tried what have been suggested online but nothing worked.I tried 
this solution:https://www.youtube.com/watch?v=h6HRGFJvzzw I also tried to 
enable WSH via 
http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html 
but all this did not solve the problem, still getting the same error.
I am running as administrator and anti virus is disabled.
Can you please guide how to fix this issue?
Thanks and regards,
Abhinav Srivastava
My bitlock installer log is as follows. OS is windows 7 (64 bit)
Log started 08/17/2018 at 00:59:05Preferred installation mode : qtTrying to 
init installer in mode qtMode qt successfully initializedExecuting 
C:\Users\Abhinav\AppData\Local\Temp/postgresql_installer_2b07efb6c7/temp_check_comspec.bat
 Script exit code: 0
Script output: "test ok"
Script stderr:

Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 Data 
Directory. Setting variable iDataDirectory to empty valueCould not find 
registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 Base 
Directory. Setting variable iBaseDirectory to empty valueCould not find 
registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 Service 
ID. Setting variable iServiceName to empty valueCould not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 Service 
Account. Setting variable iServiceAccount to empty valueCould not find registry 
key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 
Super User. Setting variable iSuperuser to empty valueCould not find registry 
key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 
Branding. Setting variable iBranding to empty valueCould not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 
Version. Setting variable brandingVer to empty valueCould not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 
Shortcuts. Setting variable iShortcut to empty valueCould not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.5 
DisableStackBuilder. Setting variable iDisableStackBuilder to empty 
value[00:59:08] Existing base directory: [00:59:08] Existing data directory: 
[00:59:08] Using branding: PostgreSQL 9.5[00:59:08] Using Super User: postgres 
and Service Account: NT AUTHORITY\NetworkService[00:59:08] Using Service Name: 
postgresql-x64-9.5Executing cscript //NoLogo 
"C:\Users\Abhinav\AppData\Local\Temp\postgresql_installer_2b07efb6c7\prerun_checks.vbs"Script
 exit code: 1
Script output: CScript Error: Can't find script engine "VBScript" for script 
"C:\Users\Abhinav\AppData\Local\Temp\postgresql_installer_2b07efb6c7\prerun_checks.vbs".
Script stderr: Program ended with an error exit code

Re: vPgSql

[Johnes Castro]

Nice,

Very useful.




De: Vlad Alexeenkov 
Enviado: sexta-feira, 17 de agosto de 2018 10:30
Para: pgsql-general@lists.postgresql.org
Assunto: vPgSql

Maybe will be useful for someone

Very simple Postgres SQL client vPgSql:

https://vsdev.ru
Gogs - Very simple PostGres SQL client vPgSql
vsdev.ru
Шаг 1 дает заготовки скриптов по созданию dblink. При их помощи или без нужно 
создать dblink к БД с которой будем сравнивать, при необходимости нужно 
установить extension




Best regards,
Vlad Alexeenkov

Re: vPgSql

[Dmitry Igrishin]

Hey Vlad
пт, 17 авг. 2018 г. в 15:31, Vlad Alexeenkov :
>
> Maybe will be useful for someone
>
> Very simple Postgres SQL client vPgSql:
>
> https://vsdev.ru
Looking nice! Thank you. But I unable to start it on Ubuntu, because
there is no bash(1) in /usr/bin.
Also, it is open source?

Re: pg_upgrade (and recovery) pitfalls

[Stephen Frost]

Greetings,

* PO (gunnar.bl...@pro-open.de) wrote:
> Stephen Frost – Thu, 16. August 2018 19:00
> > * PO (gunnar.bl...@pro-open.de) wrote:
> > > - why does a recovery, based on a recovery.conf that points to a reachable
> > primary (which obviously communicates its own timeline), still look for 
> > higher
> > timelines' history-files in the archive and tries to jump onto these
> > timelines? This doesn't seem reasoable to me at all...
> > 
> > PG is going to start from the current timeline and try to find all the
> > timelines that it could possibly play forward to and at what point the
> > timeline changes were done and then it's going to figure out which
> > timeline to go to (by default we try to stick with the currnet timeline,
> > but you can configure recovery.conf to specify a different timeline or
> > 'latest') and then it's going to request the WAL to get from where PG is
> > to the end of whichever timeline it thinks you want. That's all
> > entirely reasonable and how things are supposed to work.
> > 
> > Only once PG reaches the end up what's available through the restore
> > command does it start trying to talk to the primary. There's been some
> > discussion about how it might be nice to be able to configure PG to
> > prefer going to the primary instead, though, really, it should typically
> > be faster to replay WAL from a restore_command than to get it from the
> > primary over the network, not to mention that getting it from the
> > primary will introduce some additional load on the system.
> 
> Fair enough, and I onlöy just realised I've been carrying a 
>   recovery_target_timeline = 'latest'
> around from my predecessors. :facepalm:

That isn't necessairly a bad thing to have, especially in environments
where you're promoting replicas to be primaries and flipping things
around.

> I owe you a beer or X (in Lisbon?)!

Yes, I'll be in Lisbon, would be happy to chat over a beer.

Thanks!

Stephen


signature.asc
Description: PGP signature

vPgSql

[Vlad Alexeenkov]

Maybe will be useful for someone

Very simple Postgres SQL client vPgSql:

https://vsdev.ru

Best regards,
Vlad Alexeenkov

Re: pg_upgrade (and recovery) pitfalls

[PO]

Stephen Frost – Thu, 16. August 2018 19:00
> Greetings,

I salute you, Stephen!

TL;DR: I blundered by not spotting an easter egg of my predecessors.


> * PO (gunnar.bl...@pro-open.de) wrote:
> > Consider the following scenario/setup:
> > - 4 DB servers in 2 DCs
> > - 1 primary (in DC1)
> > - 1 sync secondary (in other DC)
> > - 2 async secondaries (distributed over DCs)
> 
> I'm a bit surprised that you're ok with the latency imposed by using
> sync replication to another data center. I'm guessing they're pretty
> close to each other?

Yep, they are, as close as they're allowed to to fulfil regulatory requirements.
Availability (and consistency) is far more relevant than performance, machines 
are at ~5% load most of the time. 

> > - General requirements are:
> > - *always* have one sync secondary online (no exceptions)
> 
> Well, you kind of have to or everything stops. ;)

Guess why we have 4 servers in each cluster ;-)

> > The "naive" idea was to shutdown all instances (starting with the primary to
> enable final syncs), run "pg_upgrade -k" on both the former primary and the
> former sync secondary, re-link the recovery.conf on the secondary, re-enable
> the "primary" IP and start both.
> > 
> > D'oh! The secondary is complaining about a different cluster identifier:
> > "FATAL: database system identifier differs between the primary and standby"
> 
> No, you can't do that.

Well, yeah, learned that the hard way. It does make sense afterall, which is 
why I called the approach "naive" in the first place.


> > (From looking at the code, I could not determine straight away when and how
> this identifier is generated, but I guess it is somewhere in initdb.c?)
> 
> Yes.
> 
> > So, as we can't rsync (no ssh...), which would probably finish in a few
> seconds, a pg_basebackup is due. Which can be a PITA when the DB size is
> scraping on a TB and you have a single 1GB/sec connection. Bye, bye,
> availability (remember the primary requirement?).
> 
> The rsync *might* finish quickly but it depends a lot on the specifics
> of your environment- for example, the rsync method doesn't do anything
> for unlogged tables, so if you have large unlogged tables you can end up
> with them getting copied over and that can take a long time, so, some
> prep work should be done to make sure you nuke any unlogged tables
> before you go through with the process (or do something similar).

Nah, nothing sophisticated like unlogged tables here ;-)))

> pg_basebackup has the unfortunate issue that it's single-threaded,
> meaning that enabling compression probably will cause the system to
> bottle-neck on the single CPU before reaching your 1Gb/s bandwidth
> limit anyway. You could parallelize the backup/restore using pgbackrest
> or, in recent versions I think, with barman, and that should at least
> get you to be able to fill the 1Gb/s pipe with compressed data for the
> backup. You're likely still looking at an hour or more though to get
> all that data copied over that small a pipe.

Yep, that's the pain about it

> 
> > ==> So by now, we're only pg_upgrade'ing the primary and follow up with a
> pg_basebackup to the secondary, planning for much longer downtimes. <==
> 
> I have to say that I probably would argue that you should really have at
> least two replicas in the same DC as the primary and then use
> quorom-based syncronous replication. Presumably, that'd also increase
> the bandwidth available to you for rebuilding the replica, reducing the
> downtime associated with that. That might also get you to the point
> where you could use the rsync method that's discussed in the pg_upgrade
> docs to get the replicas back online.
> 
> > After finishing the pg_basebackup, re-link the recovery.conf, start. 
> > The recovery finds history-files from higher timelines in the archive,
> starts to recover those (?) and then complains that the timeline doesn't match
> (don't take the numbers here too seriously, this is from a low-traffic test
> system, the fork off TL 1 was at least a year ago):
> > 
> > restored log file "0002.history" from archive
> > restored log file "0003.history" from archive
> > restored log file "0004.history" from archive
> > FATAL: requested timeline 3 is not a child of this server's history
> > DETAIL: Latest checkpoint is at 9C/36044D28 on timeline 1, but in the
> history of the requested timeline, the server forked off from that timeline at
> 69/8800.
> > 
> > This mess can probably be cleaned up manually (delete the
> 00[234].history etc. on both the secondary and the BARMAN archive),
> however to be 100% safe (or when you're unexperienced), you take another
> basebackup :/
> 
> Whoa No, this isn't good- once you've done a pg_upgrade, you're
> on a *new* cluster, really. There's no playing forward between an old
> PG server and a new one that's been pg_upgrade'd and you should really
> be using a tool that makes sure you can't end up with a messed up
> archive like that.