Hot answers tagged

21

First of all, the GUC max_connections - a setting in postgresql.conf. Heroku obviously limits this to 20 for the "Starter Tier" and 500 for the "Production Tier". The maximum number is not limited by Postgres itself, but by available system resources. Typically, performance degrades with too many concurrent connections, so even if you can set ...


14

You can easily do what @a_horse_with_no_name suggests in his comment. But there is also an interesting way to do it, using PL/pgSQL as the function language. This uses a feature of the COPY command, introduced in PostgreSQL 9.3. It can now take a command as target/source, exactly where you'd use a filename or STDIN/STDOUT in normal cases: COPY ...


7

This limit applies to the total TCP socket connections and Unix socket connections, combined, so connecting via the Unix socket won't help you if your connection count reaches the configured max. However, the slot reserved for a user with SUPER privilege is not a single slot in the way your question implies. Any time the server's current client thread ...


6

In Oracle Database, you can use Database Resource Manager (DRM) to manage memory, CPU, time and other resources. Usually your workflow is as follows: Create a resource plan Create the resource consumer groups Create the resource consumer group mappings Create the resource-plan directives You create a plan with the DBMS_RESOURCE_MANAGER.CREATE_PLAN ...


6

The end of your post is really the important bit: information regarding what best suits the needs of database administrators. So let's start there. What are your needs as a DBA? You generally have two realms of operation: system level maintenance, and database level maintenance (whereby all actions are merely maintaining the system for better ...


6

If you just need to look at something relative to $PGDATA you can use pg_ls_data SELECT pg_ls_dir('pg_xlog'); Otherwise, a simple function like this: CREATE OR REPLACE FUNCTION ls(location text) RETURNS text AS $BODY$ use warnings; use strict; my $location = $_[0]; my $output = `ls -l $location`; return($output); $BODY$ LANGUAGE ...


5

We have so many user ID locks from the front-end users. Good Problem Statement. More common than you might think. We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present. No. You don't. You need to find out why the users are locking their accounts so often and resolve the Root ...


5

ksh or bash let you script items through SQL*Plus, and you can do quite complex stuff through this. However, shell scripting tends to be a bit 'write-only' and sh and its derivatives aren't really much good for developing complex program logic. For running automated tasks they're OK. For complex client-side data manipulation, not so good (sed/awk ...


4

This depends on the flavor of *nix where you run on and whether the installation was customized or not. If something of Oracle was installed on your system, the directory /etc/oracle or /var/opt/oracle normally would have to exist. Normally they contain a file /etc/oraInst.loc or /var/opt/oracle/oraInst.log that points to the inventory that contains the ...


3

If you have firewall running, allow connections on port 27017, MongoDB default port. Not sure which RHEL version you are running so below commands might change. 1. Any connections can connect to MongoDB on port 27017 iptables -A INPUT -p tcp --dport 27017 -j ACCEPT 2. Only certain IP can connect to MongoDB on port 27017 iptables -A INPUT -s <ip-...


3

The documentation for nzsql states that to shell-out you just have to use blackslash pling, followed by the command: \! command If you need to know how to test for file size in a shell script, you can use something similar to: if [[ -f $file ]] && [[ -s $file ]]; then echo "File exists and is greater than 0 bytes" ; rm -f $file fi -f checks ...


3

Why do you want to use sendmail? Is it just because it is simpler than UTL_SMTP? You should really look into UTL_MAIL. See this: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL To send mail from an Oracle db, you might: Use UTL_TCP, way too low-level, old school, not recommended. Call Unix Sendmail (external program) using DBMS Scheduler. Kind of ...


3

I hope that you know what you are doing.... First find which segment would be a candidate to drop because it was not cleaned up by an Oracle crash: ipcs -bom Take note of the owner and group, they should be the owner of the Oracle rdbms processes. Next check the NATTCH column, should be 0 processes attached to the segment. If you still want to remove that ...


2

If you have the slow query log active, you might be able to identify a long running query during the timeframe of your load spike. This probably the only way to figure out the issue that has already occurred. You can configure a script like pt-stalk to try to capture future issues, however. Here's an example to trigger data collection if you have more ...


2

The figures in the v$sysmetric view are aggregated from v$osstat over a period of time (either 15 or 60 seconds). v$osstat figures should closely mirror what you'd see from a tool such as iostat, sar, mpstat, top or nmon, but they will never be identical as it is impossible to synchronise the performance monitoring tool data collection with the data ...


2

First of all, I should thank you for all other steps that you mentioned above. As an addition to your points, I have this one which I've been using so far. Solaris and Linux $ pgrep -lf _pmon_ 12586 ora_pmon_ee10204 ORACLE_SID is ee10204 $ pwdx 12586 12586: /u01/oracle/10.2.0.4/ee1/dbs ORACLE_HOME is /u01/oracle/10.2.0.4/ee1 HPUX $ ps -ef | grep pmon ...


2

After asking on pgsql-performance list, Jeff Janes figured out that the cause was associated to the default collation used by Postgres (see this link for more informations). MacMini was using the much performing collation while Dell T420 was using the en/US collation. T420 (Postgres 9.4.1) List of databases Name | ...


2

This is the expected behaviour on a unix system. either a process' effective user id allows to read/write a file or itseffective primary or its effective secondary group ids allow to read/write the files If you log on to a unix system the effective user id , the effective group id and the effective secondary group ids are the ids defined for your login ...


2

If you have a file which holds all the addresses (1 at a line) you can do: cat ipaddresses.txt | while read line; do ./reset_ip.sh $line /root/Desktop/BashScripts/pg_hba.conf; done or, with gawk: gawk '{ system("./reset_ip.sh $1 /root/Desktop/BashScripts/pg_hba.conf") }' ipaddresses.txt


1

You may consider something like: #!/bin/bash ... cnt=$(psql -h localhost -U postgres -d $DB_name -At -c "SELECT COUNT(*) AS USER_COUNT FROM pg_roles WHERE rolname = '${user}' ") if [[ ${cnt} -eq 0 ]] then echo 'New User ' $user else echo 'User Exists ' $user fi The error you got saying unary operator expected is indeed because the variable is ...


1

I think „UTC Timestamp“ is not the correct term since UTC is defined in terms of Leap Seconds. What is a more usual term here is TAI time or GPS time, both use no leap seconds and UTC is defined in terms of TAI (GPS is basically TAI starting at a different offset, so it has 19s less leap time). The difference of UTC time to TAI time cannot be calculated (...


1

I changed delimiter which between server and port from : to , . It fixed my problem. dbhandle <- odbcDriverConnect('driver=ODBC Driver 11 for SQL Server;server=10.XXX.XX.XX,1663;')


1

ORA-08103 object no longer exists means you were querying a segment that has since been (re)moved. The most common examples of this are: Someone truncated the table Someone did 'alter table move' Someone did 'exchange partition' all of which move a segments contents to a new place on disk, hence the data you were scanning "no longer exists"


1

If it were me, I would put everything in the database that I can. For instance, all SQL logic should go into packages/procedures, and I would control the execution of these routines via DBMS_SCHEDULER jobs if possible. Notifications based off of results from these routines can be sent via UTL_MAIL. Putting this in the database allows you to lock it down ...


1

I could fix the problem. I changed my TNS_NAMES.ORA removing ADDRESS_LIST and leaving like this: ORCL= (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=msoracle.ccccc.us-east-1.rds.amazonaws.com)(PORT=1521)) (CONNECT_DATA= (SID=ORCL) ) ) After I created a new file sqlnet.ora and added this: NAMES.DIRECTORY_PATH = (TNSNAMES) I also (just in case), ...


1

Well, I found online that the environment variable UMASK and UMASK_DIR must be set, regardless of what the command umask will output. One must add export UMASK=0640 and export UMASK=0750 to the top of their startup script, immediately before calling mysqld_safe. The leading zero is important to switch to the octal numbering system. The first thing I tried ...


1

Shell quotes typically delimit arguments or escape other special shell characters from shell interpretation. You can contain (and therefore escape) different kinds of shell-quotes within other kinds based on various rules: "''''" - a soft-quoted string can contain any number of hard-quotes. "\"" - a \ backslash can escape a "soft-quote within a "soft-quoted ...


1

1) Make sure that RDS instance has access to ec2 instance from where you are trying to connect 2) From ec2 instance connect to Amazon RDS instance using telnet $telnet If telnet fails means connection to MySQL port is not possible.


1

When exporting information from MySQL for jobs using mysqlbinlog, using sudo is convenient for me too because it is usually to fix or troubleshoot something broken and time is a concern. If you intend to re-import data extracted from MySQL then you need to work around the problem of permissions issues at some point. When creating dump files using the root ...


1

Your best chance is to take a look at /etc/oratab or /var/opt/oracle/oratab. If you are in an installation where every PATH and/or ORACLE_HOME is hardcoded in scripts then you are out of luck, as is with any other option you mentioned. Even if you happen to find a listener.ora, it could very well be that is setup for dynamic registration in which case you ...


Only top voted, non community-wiki answers of a minimum length are eligible