New answers tagged

0

Reference taken from this article. SELECT pid ,datname ,usename ,application_name ,client_hostname ,client_port ,backend_start ,query_start ,query FROM pg_stat_activity WHERE state <> 'idle' AND pid<>pg_backend_pid();


1

My understanding is that you deleted the cluster when uninstalling and it isn't recreated automatically. Recreating the cluster should solve the problem: mkdir /var/lib/postgresql/9.3/main chown postgres /var/lib/postgresql/9.3/main su postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main initdb You may need to substitute postgres ...


0

So, for some reason, this is an Azure issue. I opened port 5432, but that didn't work. In a move of desperation, I've opened 1024-65535, and now I can connect. Clearly I'm missing something related to the usage of inbound rules. While this does fix my issue, it's clearly not a best practice. Any thoughts on what I'm missing?


1

Sounds like you need to use iptables-persistent See http://postgresql.freeideas.cz/install-postgresql-9-5-on-ubuntu-14-04/


1

As you have 200 index, it would take longer than a vacuum. A reindex always depends on your OI. It seemed that it would take more than an hour. What I would suggest is: get rid of some indexes. 200 for 100 columns makes no sense. See how many of the l those you use 90% of the time. 1.1. The other queries would find their path along other indexes 1.2. ...


0

You can fix this by adding a new element to the file $WL_HOME/wlserver/server/lib/jdbcdrivers.xml I am unsure about the "UrlHelperClassname" value, though. <Driver Database="PostgreSQL - for XA" Vendor="PostgreSQL" Type="Type 4" DatabaseVersion="9.1" ForXA="true" ClassName="org.postgresql.xa.PGXADataSource" ...


2

I believe you have postgresql configured to listen only on loopbavk (localhost). You should use the forwarding like this: ssh -L 5433:localhost:5432 user@serverip Otherwise it will try to connect to public IP of your server, where is nothing listening or nothing authorized to login.


0

Luke, the message says: ", or turn off hot_standby here." Is not the case, in your stand by server (as it came from a snapshot)? I suppose you don't want to use rds, wright? If it doesn't do the job, I don't have a simple answer. It will depend on the acceptable downtime window you can have, whether this is a single application or a multi tenant one,... ...


1

You don't have to monitor or worry about RDS, Amazon monitors it for you. So long as performance meets your needs just leave it be. It's probably being used by cache - either disk or database.


1

Here's a fairly simple Fail2ban configuration for PostgreSQL based on the HOWTO linked above but fine-tuned to actually work with Ubuntu packages, catch another error condition and skip over various debug messages to make it quicker: /etc/fail2ban/filter.d/local-postgresql.conf: [Definition] failregex = <HOST>\(\d+\) FATAL: password authentication ...


0

I have seen this error when the server side user does not match the user I am trying to log in as. In your case you are trying to log in as postgres/pghost.sohonet@SOHONET while your TGT is for user/admin@SOHONET. postgres/pghost.sohonet is the service principal, it is not the user you should authenticate as. Try logging in as user/admin instead, and make ...


0

Depend on how your server was configured, you can simply change your PG_DATA variable. But you can easily do: service postgresql stop mv /var/lib/pgsql/ /data/pgsql ln -s /data/pgsql /var/lib/pgsql service postgresql start It will do the job.


0

You should create the uauvuro0s8b9v4 user prior to restore the database or the pg_restore will raise such errors (as this user's owns a lot of relations, in your case). In psql try: CREATE USER uauvuro0s8b9v4 WITH PASSWORD '12334444';


0

As @user13185, you should use pg_dump / pg_restore. But it's a good practice to rename your database prior to start the dump. In order to do that, all connections to that database should be disconnected.


1

Your locale is mis-set. You have LANG defined as en_US.UTF-8, but everything else is defined as C. These are rather different, and mixing them will cause all sorts of weirdness. Do what the error message told you: set LC_ALL to the correct locale (and set all the rest of them too).


1

From here: If you're completely wiping & reinstalling a Postgres DB, when running initdb like: service postgresql-9.2 initdb -E 'UTF8' --pgdata="/foo/bar/" you can encounter this service error: Data directory is not empty! [FAILED] To fix it (and this is the nuclear option -- all db data is wiped!) On Amazon Linux (2014-x): rm -rf ...


2

Your config setup looks ok, however how you are trying to connect to the service doesn't. If you don't specify a custom port, it will try to connect via the default port. Try using the -p flag to define that you want to connect via a specific port: psql -p 4000


0

It's a bit late to answer this question now. Nevertheless, I came across this issue past week and couldn't find the answer in the context of "repmgr". pg_ctl resides in the bin directory of your Postgres installation. The bin directory is not in the PATH environment variable. You can not set the environment variable either which makes this problem a tricky ...


4

Set up your maintenance and backup window in the RDS instance control panel. Some information is available here.


0

You can do a physical/file level backup on PostgreSQL, albeit it is not standard practice (I'll try very hard to make pg_dump work; have you asked on the mailing list?) As another option, have you considered a replicated setup, with "live" point-in-time backup/recovery? Anyway, to take a physical backup with minimal downtime you had to: stop PostgreSQL ...



Top 50 recent answers are included