New answers tagged

0

For newer versions of PostgreSQL: select pg_terminate_backend(pid) from pg_stat_activity where usename = 'YOURDATABASEUSERNAME*' and state = 'idle' and query_start < current_timestamp - interval '5 minutes' ; The above will help you terminate your idle connections. I had the same issue but it turned out to be a problem with my Flask and SQLAlchemy ...


0

In the service file (/etc/init.d/<service>) you can specify after which service PostGre must start. It would be like this : #! /bin/sh ### BEGIN INIT INFO # Provides: sshd # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: # Short-Description: OpenBSD ...


0

What I've learned on zfs-discuss and during my experiments: zfs_prefetch_disable=1 - significantly degrades performance for seq scans - don't disable it zfs_nocacheflush=1 - leads to data corruption - don't enable it ZFS config that works for me and is even faster than ext4 for seq reads: recordsize=8K pg_data_95 recordsize=8K pg_wal_95 atime=off ...


1

This command: ssh -L 54320:Server_A:5432 user@Server_B looks good, but then when doing this: psql -p 54320 -d db_name -U user the ssh tunnel is not used, because by default on Unix, psql connects to a Unix domain socket, like suggested by the error message you mention ("...accepting connections on Unix domain socket...") You're just missing a -h ...


4

No downtime at all is not possible without some clustering magic. Some other possibilities: use pg_upgrade with the --link option. With this option, the original DB files are not copied, rather they are hard-linked to the new directory, greatly speeding up the process. Please pay attention that this will permanently alter the source DB files. use pg_dump ...


0

Had the same problem, the port is somewhere between 25000-27500. You could keep trying to narrow it down but that seemed good enough for me for now - better than exposing every port. I also have another rule that leaves port 5432 open.


0

Make sure your firewall is not blocking traffic. Append the following rules to your iptables (change X.X.X.X to your server IP address): # iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d X.X.X.X --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT # iptables -A OUTPUT -p tcp -s X.X.X.X --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ...


0

Option 1: From the command-line shell su - postgres /usr/bin/pg_ctl reload Option 2: Using SQL SELECT pg_reload_conf(); Using either option will not interrupt any active queries or connections to the database, thus applying these changes seemlessly.


0

centos7 uses firewall-cmd out of the box, unless you have disabled it. I assume you have not because you would have mentioned it otherwise. With that assumption in place, then you can simply run this on your postgresql host: firewall-cmd --add-service=postgresql Once you have verified that it works, then you can run it again with the permanent switch to ...


0

you need to modify your dbexport.php in /usr/share/phppgadmin (on Ubuntu) modify line from passthru($cmd); to echo passthru($cmd); modify line $cmd = $exe . " -i"; to $cmd = $exe; it will be enough. Cause parameter -i seems to be deprecated in postgres 9.5 , he gave error for me.


3

A database connection is a very critical part of any infrastructure as it usually contains much more data than the clients can/should see. You want to protect this as much as possible, which means that you want to prevent anyone from even attempting to connect to it and also want to completely encrypt the traffic so no one can eavesdrop on it, and optimally ...


0

Say your tablespaces are ts1, ts2, and ts3, where ts3 is the smaller one. ALTER DATABASE your_database SET temp_tablespaces = ts1, ts1, ts2, ts2, ts3 Looks like duplicate values in the temp_tablespaces list are kept, making Postgres more likely to write a temp file those tablespaces, according to weighted random choice. Transactions use the tablespaces ...


0

Looked into /var/log/syslog and figured out that it was an issue with systemd startup sequence. Added After=network-online.target line into /lib/systemd/system/postgresql@.service to [Unit] section So it looks like that: [Unit] Description=PostgreSQL Cluster %i ConditionPathExists=/etc/postgresql/%I/postgresql.conf PartOf=postgresql.service ...


1

osm2pgsql is not yet packaged in nix, so a first step would be to package it. As far as I see you use osm2pgsql tool as a command line utility. Adding it to environment.systemPackages should make it available in your $PATH.


1

The default postgresql configuration is not good for bulk import of data. The main thing you need to do before importing that much data is to increase max_wal_size to reduce the number of times postgresql will need to perform a checkpoint during the import. Also, if you are certain about the quality of the data, import it within a transaction. See https://...


-3

You should just uncomment listener_adress and also if there are instance that there are security issues, you may configure pghba.conf to restrict addresses that you want to have authority to your postgres.



Top 50 recent answers are included