Menu
▾
▴
[Flightgear-commitlogs] FlightGear Scenery/Web tools branch, master, updated. 2abb15d858b00e9dc7eef2c8de88f35cb409bbda
|
From: Flightgear-commitlogs <ma...@hy...> - 2015-11-09 12:09:07
|
The branch, master has been updated
- Log -----------------------------------------------------------------
commit 2abb15d858b00e9dc7eef2c8de88f35cb409bbda
Author: Martin Spott
Date: Mon Nov 9 04:00:08 2015 -0800
Local database on non-standard port number.
commit 7ed7d6bf21c48b0763667da655bb1a948696e3dd
Author: Martin Spott
Date: Mon Nov 9 03:53:31 2015 -0800
Add PgBouncer config.
commit dc630d803b17bc0f1727a50e8546ccc91f864154
Author: Martin Spott
Date: Mon Nov 9 03:52:01 2015 -0800
Local database on non-standard port number.
- Summary -------------------------------------------------------------
WWW/mapserver/dlaction.sh | 4 +-
WWW/mapserver/dlsingle.psp | 2 +-
pgbouncer/pgbouncer.ini | 296 +++++++++++++++++++++++++++++++++++++++++++++
pgbouncer/userlist.txt | 1 +
4 files changed, 300 insertions(+), 3 deletions(-)
create mode 100644 pgbouncer/pgbouncer.ini
create mode 100644 pgbouncer/userlist.txt
- Diff ----------------------------------------------------------------
diff --git a/WWW/mapserver/dlaction.sh b/WWW/mapserver/dlaction.sh
index c13b043..6a47747 100755
--- a/WWW/mapserver/dlaction.sh
+++ b/WWW/mapserver/dlaction.sh
@@ -29,7 +29,7 @@ UUID=${1}
PGHOST=localhost
PGUSER=webuser
PGDATABASE=landcover
-PSQL="psql -h ${PGHOST} -d ${PGDATABASE} -U webuser -tA -c"
+PSQL="psql -h ${PGHOST} -p 5433 -d ${PGDATABASE} -U webuser -tA -c"
BASEDIR=/home/fgscenery/GIT
PGSQL2SHP=/usr/bin/pgsql2shp
DUMPDIR=${BASEDIR}/../SHPdump/${UUID}
@@ -66,7 +66,7 @@ DumpSingleLayer() {
TABLE=${2}
fi
${PGSQL2SHP} -f ${DUMPDIR}/${1}.shp \
- -h ${PGHOST} -u ${PGUSER} -g wkb_geometry -b -r ${PGDATABASE} \
+ -h ${PGHOST} -p 5433 -u ${PGUSER} -g wkb_geometry -b -r ${PGDATABASE} \
"SELECT * FROM ${TABLE} \
WHERE wkb_geometry && \
(SELECT wkb_geometry FROM download WHERE uuid = '${UUID}') ${3}"
diff --git a/WWW/mapserver/dlsingle.psp b/WWW/mapserver/dlsingle.psp
index c495dea..a4b08bf 100644
--- a/WWW/mapserver/dlsingle.psp
+++ b/WWW/mapserver/dlsingle.psp
@@ -34,7 +34,7 @@ import subprocess
import psycopg2
-db_params = {"host":"localhost", "database":"landcover", "user":"webuser"}
+db_params = {"host":"localhost", "port":"5433", "database":"landcover", "user":"webuser"}
try:
db_conn = psycopg2.connect(**db_params)
except:
diff --git a/pgbouncer/pgbouncer.ini b/pgbouncer/pgbouncer.ini
new file mode 100644
index 0000000..6428485
--- /dev/null
+++ b/pgbouncer/pgbouncer.ini
@@ -0,0 +1,296 @@
+;; database name = connect string
+;;
+;; connect string params:
+;; dbname= host= port= user= password=
+;; client_encoding= datestyle= timezone=
+;; pool_size= connect_query=
+[databases]
+
+; foodb over unix socket
+;foodb =
+
+; redirect bardb to bazdb on localhost
+;bardb = host=localhost dbname=bazdb
+
+; acceess to dest database will go with single user
+;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
+
+; use custom pool sizes
+;nondefaultdb = pool_size=50 reserve_pool_size=10
+
+; fallback connect string
+;* = host=testserver
+
+landcover = host=eclipse.optiputer.net port=5432 dbname=landcover
+scenemodels = host=localhost port=5433 dbname=scenemodels
+
+;; Configuration section
+[pgbouncer]
+
+;;;
+;;; Administrative settings
+;;;
+
+logfile = /var/log/postgresql/pgbouncer.log
+pidfile = /var/run/postgresql/pgbouncer.pid
+
+;;;
+;;; Where to wait for clients
+;;;
+
+; ip address or * which means all ip-s
+listen_addr = 127.0.0.1
+listen_port = 5432
+
+; unix socket is also used for -R.
+; On debian it should be /var/run/postgresql
+;unix_socket_dir = /tmp
+;unix_socket_mode = 0777
+;unix_socket_group =
+unix_socket_dir = /var/run/postgresql
+
+;;;
+;;; Authentication settings
+;;;
+
+; any, trust, plain, crypt, md5
+auth_type = trust
+;auth_file = /8.0/main/global/pg_auth
+auth_file = /etc/pgbouncer/userlist.txt
+
+;; Query to use to fetch password from database. Result
+;; must have 2 columns - username and password hash.
+;auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
+
+;;;
+;;; Users allowed into database 'pgbouncer'
+;;;
+
+; comma-separated list of users, who are allowed to change settings
+;admin_users = user2, someadmin, otheradmin
+
+; comma-separated list of users who are just allowed to use SHOW command
+;stats_users = stats, root
+stats_users = webuser
+
+;;;
+;;; Pooler personality questions
+;;;
+
+; When server connection is released back to pool:
+; session - after client disconnects
+; transaction - after transaction finishes
+; statement - after statement finishes
+pool_mode = session
+
+;
+; Query for cleaning connection immediately after releasing from client.
+; No need to put ROLLBACK here, pgbouncer does not reuse connections
+; where transaction is left open.
+;
+; Query for 8.3+:
+; DISCARD ALL;
+;
+; Older versions:
+; RESET ALL; SET SESSION AUTHORIZATION DEFAULT
+;
+; Empty if transaction pooling is in use.
+;
+server_reset_query = DISCARD ALL
+
+; Whether server_reset_query should run in all pooling modes.
+; If it is off, server_reset_query is used only for session-pooling.
+;server_reset_query_always = 1
+
+;
+; Comma-separated list of parameters to ignore when given
+; in startup packet. Newer JDBC versions require the
+; extra_float_digits here.
+;
+;ignore_startup_parameters = extra_float_digits
+
+;
+; When taking idle server into use, this query is ran first.
+; SELECT 1
+;
+;server_check_query = select 1
+
+; If server was used more recently that this many seconds ago,
+; skip the check query. Value 0 may or may not run in immediately.
+;server_check_delay = 30
+
+;; Use <appname - host> as application_name on server.
+;application_name_add_host = 0
+
+;;;
+;;; Connection limits
+;;;
+
+; total number of clients that can connect
+#max_client_conn = 100
+max_client_conn = 300
+
+; default pool size. 20 is good number when transaction pooling
+; is in use, in session pooling it needs to be the number of
+; max clients you want to handle at any moment
+default_pool_size = 20
+
+;; Minimum number of server connections to keep in pool.
+;min_pool_size = 0
+
+; how many additional connection to allow in case of trouble
+;reserve_pool_size = 5
+
+; if a clients needs to wait more than this many seconds, use reserve pool
+;reserve_pool_timeout = 3
+
+; how many total connections to a single database to allow from all pools
+;max_db_connections = 50
+;max_user_connections = 50
+
+; If off, then server connections are reused in LIFO manner
+;server_round_robin = 0
+
+;;;
+;;; Logging
+;;;
+
+;; Syslog settings
+;syslog = 0
+;syslog_facility = daemon
+;syslog_ident = pgbouncer
+
+; log if client connects or server connection is made
+;log_connections = 1
+
+; log if and why connection was closed
+;log_disconnections = 1
+
+; log error messages pooler sends to clients
+;log_pooler_errors = 1
+
+;; Period for writing aggregated stats into log.
+;stats_period = 60
+
+;; Logging verbosity. Same as -v switch on command line.
+;verbose=0
+
+;;;
+;;; Timeouts
+;;;
+
+;; Close server connection if its been connected longer.
+;server_lifetime = 1200
+
+;; Close server connection if its not been used in this time.
+;; Allows to clean unnecessary connections from pool after peak.
+;server_idle_timeout = 60
+
+;; Cancel connection attempt if server does not answer takes longer.
+;server_connect_timeout = 15
+
+;; If server login failed (server_connect_timeout or auth failure)
+;; then wait this many second.
+;server_login_retry = 15
+
+;; Dangerous. Server connection is closed if query does not return
+;; in this time. Should be used to survive network problems,
+;; _not_ as statement_timeout. (default: 0)
+;query_timeout = 0
+
+;; Dangerous. Client connection is closed if the query is not assigned
+;; to a server in this time. Should be used to limit the number of queued
+;; queries in case of a database or network failure. (default: 0)
+;query_wait_timeout = 0
+
+;; Dangerous. Client connection is closed if no activity in this time.
+;; Should be used to survive network problems. (default: 0)
+;client_idle_timeout = 0
+client_idle_timeout = 60
+
+;; Disconnect clients who have not managed to log in after connecting
+;; in this many seconds.
+;client_login_timeout = 60
+client_login_timeout = 20
+
+;; Clean automatically created database entries (via "*") if they
+;; stay unused in this many seconds.
+; autodb_idle_timeout = 3600
+
+;; How long SUSPEND/-R waits for buffer flush before closing connection.
+;suspend_timeout = 10
+
+;; Close connections which are in "IDLE in transaction" state longer than
+;; this many seconds.
+;idle_transaction_timeout = 0
+
+;;;
+;;; Low-level tuning options
+;;;
+
+;; buffer for streaming packets
+;pkt_buf = 2048
+
+;; man 2 listen
+;listen_backlog = 128
+
+;; Max number pkt_buf to process in one event loop.
+;sbuf_loopcnt = 5
+
+;; Maximum Postgres protocol packet size.
+;max_packet_size = 2147483647
+
+;; networking options, for info: man 7 tcp
+
+;; Linux: notify program about new connection only if there
+;; is also data received. (Seconds to wait.)
+;; On Linux the default is 45, on other OS'es 0.
+;tcp_defer_accept = 0
+
+;; In-kernel buffer size (Linux default: 4096)
+;tcp_socket_buffer = 0
+
+;; whether tcp keepalive should be turned on (0/1)
+;tcp_keepalive = 1
+
+;; following options are Linux-specific.
+;; they also require tcp_keepalive=1
+
+;; count of keepaliva packets
+;tcp_keepcnt = 0
+
+;; how long the connection can be idle,
+;; before sending keepalive packets
+;tcp_keepidle = 0
+
+;; The time between individual keepalive probes.
+;tcp_keepintvl = 0
+
+;; DNS lookup caching time
+;dns_max_ttl = 15
+
+;; DNS zone SOA lookup period
+;dns_zone_check_period = 0
+
+;; DNS negative result caching time
+;dns_nxdomain_ttl = 15
+
+;;;
+;;; Random stuff
+;;;
+
+;; Hackish security feature. Helps against SQL-injection - when PQexec is disabled,
+;; multi-statement cannot be made.
+;disable_pqexec=0
+
+;; Config file to use for next RELOAD/SIGHUP.
+;; By default contains config file from command line.
+;conffile
+
+;; Win32 service name to register as. job_name is alias for service_name,
+;; used by some Skytools scripts.
+;service_name = pgbouncer
+;job_name = pgbouncer
+
+;; Read additional config from the /etc/pgbouncer/pgbouncer-other.ini file
+;%include /etc/pgbouncer/pgbouncer-other.ini
diff --git a/pgbouncer/userlist.txt b/pgbouncer/userlist.txt
new file mode 100644
index 0000000..c364960
--- /dev/null
+++ b/pgbouncer/userlist.txt
@@ -0,0 +1 @@
+"webuser" ""
-----------------------------------------------------------------------
FlightGear Scenery/Web tools
|
