Hot answers tagged

28

Use the psql shell and: \deu[+] [PATTERN] such as: postgres=# \deu+ List of user mappings Server | User name | FDW Options --------+-----------+------------- (0 rows) And for all users: postgres=# \du List of roles Role name | Attributes | Member of ------------+---------------...


13

Check out Why does the 'bin' user need a login shell? It says this pattern for system users is Common in Debian, and not so much in other distributions. Considered a bug / genuine security issue by several people. Required in order to run cron jobs as that user, and perhaps also by some scripts if they use su -c to run as this user. It should be ...


11

The short version You can safely install the PostgreSQL package from the backports. You'll probably get a stable piece of software that will not nuke your system. The longer version Debian comes in three flavours: stable, testing and unstable. See Debian Releases. You are apparently running Debian stable, which is the preferred release for a production ...


6

You have a bigger problem than Out of Disk my friend! This is a User Defined Function exploit which takes advantage of PostgreSQL's large objects. (lo_) functions. On my server it is a trojan that creates a proxy to baby0119.com over port 80. It was installed as your postgres user over your postgres port 5432. Check your 'postgres' database for a ...


6

I think the naming convention might simply be backwards from what you expect there: there's a package postgresql-server The programs needed to create and run a PostgreSQL server and a package postgresql PostgreSQL client programs (and postgresql does not have a dependency on postgresql-server, at least not in CentOS6).


5

The same way you'd time any other shell command: use the time command. $ time pg_restore ... real 1m0.000s user 1m0.000s sys 0m0.000s Here, real is the elapsed wall clock time, which is probably the only meaningful value, since most of the work is being done in a separate process (the PostgreSQL server).


5

It's likely the result of this postgres bug: https://wiki.postgresql.org/wiki/May_2015_Fsync_Permissions_Bug


5

On Linux and with OpenJDK at least, the value returned by exitValue() is the same as what a shell like zsh or bash and most sh implementations (but not ksh93 or yash) would assign to its $? variable. That is, it's: if the process exited with exit(n) or return n from main(): the lower 8 bits of n (n & 0xFF). if the process was killed by signal n: n + ...


4

Use the PGPASSWORD environment variable. For instance: PGPASSWORD=<password> psql -U postgres -c "<postgresql query>" Source


4

If you do not specify the hostname psql assumes domain socket connections. As per the man page: If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets. Try adding -h localhost to the psql command line. As to the Oracle error, I would ...


4

# systemctl start postgresql.service Some environments would translate service <name> start to systemctl start <name>.service, but you don't have to rely on it.


4

When installing from source, you will need to add a systemd unit file that works with the source install. For RHEL, Fedora my unit file looks like: /usr/lib/systemd/system/postgresql.service [Unit] Description=PostgreSQL database server After=network.target [Service] Type=forking User=postgres Group=postgres # Where to send early-startup messages from ...


4

If you type the following: sudo su - postgres after installing postgresql-server, that should take you right to the home directory of postgres and will have the configuration files you are looking for. Usually in a RHEL environment, the configuration files would be stored in /var/lib/pgsql/. On my test environment it is stored in /var/lib/pgsql/9.1/data.


4

You can specify the individual columns to fill in the COPY command: COPY table_name [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ] (http://www.postgresql.org/docs/9.1/static/sql-copy.html) Therefore, specify your CSV columns explicitly in the COPY, leaving out the bigserials column.


3

I installed Postgresql with the apt-get command. Do I have to create a postgres-user and change ownership of the installed postgresql files? No, you don't need to create postgre-user, postgresql package will automatically create the users that require. You can check using : getent passwd postgres or just grep in /etc/passwd file I see that the ...


3

When I use the useradd command I don't specify a password. [...] Does this mean that the user is disabled until it is done? Yes, if you don't specify a password hash using useradd's option -p, password-based login to the newly created account will be locked until you set a password using passwd. I installed Postgresql with the apt-get command. Do I ...


3

You might want to try the directions described in this post titled: Install latest PostgreSQL on Linux Mint. General steps: create /etc/apt/sources.list.d/pgdg.list containing: deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main Import the repository signing key, and update the package lists $ wget --quiet -O - https://www.postgresql.org/...


3

In my case I had two sets of databases some running on the old version of Postgresql 8.4 and other running on version 9.1. What I did was to locate pg_dump in Linux machine using the locate command below $ locate pg_dump /usr/bin/pg_dump /usr/bin/pg_dumpall /usr/lib/postgresql/8.4/bin/pg_dump /usr/lib/postgresql/8.4/bin/pg_dumpall /usr/lib/postgresql/9.1/...


3

apt-get will upgrade to the packages available in the repository from the source that apt is configured to use. The postgresql-9.1 package is version 9.1.4 in the squeeze-backports repository. Add the following to /etc/apt/sources.list.d/backports.sources.list deb http://backports.debian.org/debian-backports squeeze-backports main contrib non-free Then ...


3

It would appear that you have your your syntax a bit backwards. rc.d usage: rc.d action daemon ... e.g: rc.d list rc.d help rc.d start sshd gpm So you would want to do rc.d start postgresql The rc.d script is simply for convenience. It makes starting multiple ...


3

The best method to incremental backup Postgres is to use periodic hot physical backups and continuous WAL archiving. http://www.postgresql.org/docs/9.1/static/continuous-archiving.html The periodic physical backup can be done with rsync using the --copy-dest= option to take advantage of any previous copy reusing unchanged files. This is a very effective ...


3

The protocol is plain TCP/IP. From posgresql documentation about "frontends" and "backends" protocol: PostgreSQL uses a message-based protocol for communication between frontends and backends (clients and servers). The protocol is supported over TCP/IP and also over Unix-domain sockets. Port number 5432 has been registered with IANA as the customary TCP ...


3

Your line: psql -U postgres -d ebay_sold -c "UPDATE test_table SET title = regexp_replace(title, '"', '', 'g')" is the problem. You open a double-quoted string at "UPDATE, but it is closed earlier than you think it is, causing you to attempt to run the following as SQL: UPDATE test_table SET title = regexp_replace(title, ' This is clearly not valid. ...


3

On a debian system, postgresql files and directories should be owned by user postgres, in group postgres, with permissions of either 0700 (directories) or 0600 (files). If they're not that, you can repair perms & ownership with: sudo chown -R postgres:postgres /var/lib/postgresql/9.4/ sudo chmod -R u=rwX,go= /var/lib/postgresql/9.4/ Note the capital ...


3

Each VM can host a theoretically unlimited number of databases. The practical limits are the usual suspects - memory, disk, and CPU resource availability for the connections and internal processing said database(s) require. Simply create the new database and give access to it through the normal PostgreSQL commands (GRANT statements).


2

If the database servers are running, ask them: psql -l mysql <<< 'show databases' Just searching the filesystem for relational databases is not a trivial task.


2

Edit your PATH so that /usr/pgsql-9.1/bin/ (the newer one) comes before /usr/bin/. A less package-manager-safe safe alternative is to move/remove/rename the psql in /usr/bin/ and create a symlink in /usr/bin/ to the new one (not tested): > cd /usr/bin # move the old one however you like > ln -s /usr/pgsql-9.1/bin/psql .


2

Couple of options. Download .deb 9.1 from the Postgres site Take a look at this page titled: Linux downloads (Debian) - PostgreSQL. You can either download an updated .deb from the site directly, or re-point to their repository, and do a command like this: apt-get install postgresql-9.1 Debian backports You may be able to find specific versions here, ...


2

Add this to your sources.list: deb http://ftp.de.debian.org/debian/ wheezy main non-free contrib deb-src http://ftp.de.debian.org/debian/ wheezy main non-free contrib Make sure you use the appropriate mirror for your location. Run aptitude update or apt-get update and finish the installation. The interactive mode of aptitude is quite useful to solve ...


2

Like the comments in this answer suggest, just edit the file /etc/shadow as root (e.g. run sudo nano /etc/shadow in a terminal) and replace the hashed password in postgres's line with *. (If you don't have that line or that file, it'd be interesting to know which Linux distribution and nsswitch settings you use.)



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