PostgreSQL - Revision history

Johnno: /* Troubleshooting */

2024-09-19T00:24:17Z

Troubleshooting

2023-09-07T20:11:51Z

Documentation: use current docs

2021-08-15T22:20:24Z

Documentation

2020-12-14T22:26:52Z

Backslash Commands

2014-10-24T02:24:07Z

Troubleshooting

2014-10-24T02:23:19Z

Restore

2013-02-08T02:11:14Z

2009-05-12T23:54:12Z

Troubleshooting

2009-01-29T21:57:38Z

Creating Users and Databases

2008-11-17T10:13:06Z

Links to other pages

@@ Line 169: / Line 169: @@
 :You may need to stop Apache before you can drop a database, otherwise PostgreSQL will complain that it is in use.
 :Or, you can create a ''/usr/local/bin/pg_kill_connections'' script:
- #!/bin/sh
- DATABASE="$1"
+:<syntaxhighlight lang="bash">
 #!/bin/sh
- if [ -z "${DATABASE}" ];then
+DATABASE="$1"
-     echo "Usage: pg_kill_connections dbname"
-     exit 1;
+if [ -z "${DATABASE}" ];then
- fi
+    echo "Usage: pg_kill_connections dbname"
+    exit 1;
- for i in $(su postgres -c \
+fi
-        "psql -tc \"SELECT procpid FROM pg_stat_activity WHERE datname='${DATABASE}'\" \
-         template1 | xargs -r kill"); do
+for i in $(su postgres -c \
-     kill $i
+       "psql -qt -c \"\\timing false\" \
- done
+             -c \"SELECT pid FROM pg_stat_activity WHERE datname='${DATABASE}'\" \
 ;How do I rename a database?

@@ Line 1: / Line 1: @@
 == Documentation ==
-The online [http://www.postgresql.org/docs/ PostgreSQL Manual] is actually quite awesome and worth bookmarking.
+The online [http://www.postgresql.org/docs/current/ PostgreSQL Manual] is actually quite awesome and worth bookmarking.
 == Install and Configure ==

@@ Line 1: / Line 1: @@
 == Documentation ==
-The online [http://www.postgresql.org/docs/9.1/interactive/index.html PostgreSQL Manual] is actually quite awesome and worth bookmarking.
+The online [http://www.postgresql.org/docs/ PostgreSQL Manual] is actually quite awesome and worth bookmarking.
 == Install and Configure ==

@@ Line 138: / Line 138: @@
 |-
 | ''\cd <path>''     || Change directory (useful for \i)
 |-
 | ''\c <database>''  || Connect to another database

@@ Line 165: / Line 165: @@
 ;I can't drop the database!
-:You may need to stop apache before you can drop a database as PostgreSQL will complain that it is in use.
+:You may need to stop Apache before you can drop a database, otherwise PostgreSQL will complain that it is in use.
 :Or, you can create a ''/usr/local/bin/pg_kill_connections'' script:
   #!/bin/sh

@@ Line 156: / Line 156: @@
 === Restore ===
-During a restore, the ownership is set to whichever user the restore command connects to the database as. So to restore a dump file into an existing database (-d), without using ownerships from the file (-O), but specifying the connecting user as the owner:
+During a restore, the ownership is set to whichever user the restore command connects to the database as. So to restore a dump file into an existing database (-d), without using ownerships from the file (-O), but specifying the connecting user (-U) as the owner:
   pg_restore -O -U <username> -d <databasename> <dumpfile>

@@ Line 1: / Line 1: @@
 == Documentation ==
-The online [http://www.postgresql.org/docs/8.2/interactive/index.html PostgreSQL Manual] is actually quite awesome and worth bookmarking.
+The online [http://www.postgresql.org/docs/9.1/interactive/index.html PostgreSQL Manual] is actually quite awesome and worth bookmarking.
 == Install and Configure ==
 Assuming Debian or Ubuntu, installation is:
-  sudo apt-get install postgresql-8.2 postgresql-client-8.2
+  sudo apt-get install postgresql postgresql-client
 === Check The Configuration ===
-You may end up with (and may require) several different versions of PostgreSQL on one box. If this is so, check that they are running on separate ports. The port is specified in <tt>/etc/postgresql/8.2/main/postgresql.conf</tt>. For a development environment, we can make life simple by making a change to <tt>/etc/postgresql/8.2/main/pg_hba.conf</tt>:
+You may end up with (and may require) several different versions of PostgreSQL on one box. If this is so, check that they are running on separate ports. The port is specified in <tt>/etc/postgresql/9.1/main/postgresql.conf</tt>. For a development ('''NOT''' production!) environment, we can make life simple by making a change to <tt>/etc/postgresql/9.1/main/pg_hba.conf</tt>:
   local  all     all                            trust

@@ Line 23: / Line 23: @@
 PostgreSQL system administration is somewhat different in approach to MySQL, but actually much less retarded once you get the hang of it.
-With PostgreSQL creating users and databases is done with shell level commands <tt>createuser</tt> and <tt>createdb</tt>, rather than lame non-standard SQL commands.
+With PostgreSQL creating users and databases is done with shell level commands <tt>createuser</tt> and <tt>createdb</tt>, rather than lame non-standard SQL commands (although you can also use SQL-ish statements like MySQL if you insist, but they are not covered here).
 === Make Yourself A Superuser ===

← Older revision		Revision as of 10:13, 17 November 2008
Line 172:		Line 172:
	;How do I concatenate strings?		;How do I concatenate strings?
	:SELECT 'Hello' \|\| ' ' \|\| 'World';		:SELECT 'Hello' \|\| ' ' \|\| 'World';
		+
		+	== See Also ==
		+	* [[PostgreSQL/From_MySQL]]
		+	* [[PostgreSQL full text searching]]