Difference between revisions of "Freeside:1.7:Documentation:Administration:PostgreSQL Schema"

From Freeside
Jump to: navigation, search
(New page: = Introduction = The instructions pertain specifically to using a non-standard PostgreSQL "schema" name - a sub-partition within a database (see [http://www.postgresql.org/docs/current/st...)
 
(Introduction)
 
(3 intermediate revisions by the same user not shown)
Line 3: Line 3:
 
The instructions pertain specifically to using a non-standard PostgreSQL "schema" name - a sub-partition within a database (see [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schemas] in the PostgreSQL documentation).  They do not refer to using custom tables or columns (i.e. <b>not</b> the  [http://en.wikipedia.org/wiki/Database_schema Database schema] in the traditional/generic sense).
 
The instructions pertain specifically to using a non-standard PostgreSQL "schema" name - a sub-partition within a database (see [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schemas] in the PostgreSQL documentation).  They do not refer to using custom tables or columns (i.e. <b>not</b> the  [http://en.wikipedia.org/wiki/Database_schema Database schema] in the traditional/generic sense).
  
Normally PostgreSQL defaults to the "public" schema.  In some environments this may be deemed undesirable, even though there is no actual logical reason for this.  <b>There is nothing special about the "public" schema</b> as opposed to any other namePostgreSQL stores system objects in the "pg_catalog" schema.
+
Normally PostgreSQL defaults to the "public" schema.  In some environments this may be deemed undesirable, even though there is no actual logical reason for this.  <b>There is nothing special about the "public" schema</b> as opposed to any other name (PostgreSQL stores system objects in the "pg_catalog" schema).
  
 
= Setting the schema name in Freeside =
 
= Setting the schema name in Freeside =
  
Edit the <code>/usr/local/etc/freeside/secrets</code> (or in installations installed from RPM or Debian packages, the <code>/etc/freeside/secrets</code> file).  Add a fourth line with the schema name.  Make sure that the schema name appears on the <b>fourth</b> line, even if you are not using a database password and the third line is blank.
+
Edit the <code>/usr/local/etc/freeside/secrets</code> file (or in installations installed from RPM or Debian packages, the <code>/etc/freeside/secrets</code> file).  Add a fourth line with the schema name.  Make sure that the schema name appears on the <b>fourth</b> line, even if you are not using a database password and the third line is blank.
  
 
= Setting the search path in PostgreSQL =
 
= Setting the search path in PostgreSQL =
Line 14: Line 14:
  
 
<pre>
 
<pre>
ALTER USER freeside SET search_path = 'my_custom_schema';
+
ALTER USER freeside SET search_path TO custom_schema_name;
 
</pre>
 
</pre>

Latest revision as of 12:16, 3 June 2009

Introduction

The instructions pertain specifically to using a non-standard PostgreSQL "schema" name - a sub-partition within a database (see Schemas in the PostgreSQL documentation). They do not refer to using custom tables or columns (i.e. not the Database schema in the traditional/generic sense).

Normally PostgreSQL defaults to the "public" schema. In some environments this may be deemed undesirable, even though there is no actual logical reason for this. There is nothing special about the "public" schema as opposed to any other name (PostgreSQL stores system objects in the "pg_catalog" schema).

Setting the schema name in Freeside

Edit the /usr/local/etc/freeside/secrets file (or in installations installed from RPM or Debian packages, the /etc/freeside/secrets file). Add a fourth line with the schema name. Make sure that the schema name appears on the fourth line, even if you are not using a database password and the third line is blank.

Setting the search path in PostgreSQL

The hack described above only sets the schema for the cases when Freeside uses the schema name explicitly. You are responsible yourself for ensuring that your non-standard schema appears before any other existing schema in the schema search path. The usual way to do this is:

ALTER USER freeside SET search_path TO custom_schema_name;