https://wiki.postgresql.org/api.php?action=feedcontributions&user=Wibrt&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-28T22:20:03ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=Apt/FAQ&diff=20705Apt/FAQ2013-09-03T16:14:15Z<p>Wibrt: /* I want only specific packages from this repository */</p>
<hr />
<div>==apt.postgresql.org FAQ==<br />
<br />
==General Questions==<br />
<br />
===What's the difference between the PostgreSQL packages provided by Debian/Ubuntu and by the PGDG apt repository?===<br />
<br />
Pretty little, actually. The PGDG packages are built from the same source as the Debian packages (Ubuntu is using the same source for the PostgreSQL server packages as well).<br />
We try to follow Debian unstable's versions as close as possible, i.e. the packages available here are the same, just rebuilt with a ".pgdg" appended to the version number.<br />
We are also using the same postgresql-common infrastructure packages.<br />
<br />
===Should I use Debian/Ubuntu's packages, or the PGDG packages?===<br />
<br />
Debian and Ubuntu only ship one PostgreSQL server version per release. For example, there is only PostgreSQL 8.4 in Debian Squeeze/6.0. If that is the version you want, use it. There is no real difference.<br />
If you want a different PostgreSQL server version, use the PGDG packages.<br />
<br />
Same goes for extension module packages, except that we are updating to new upstream versions earlier (following Debian unstable), while Debian/Ubuntu will only accept patches for critical problems in a released distribution.<br />
<br />
===What about Debian backports?===<br />
<br />
[[http://backports.debian.org Debian backports]] provide newer package versions for released distributions, similar to what the PGDG apt repository does.<br />
The scope of the backports archive is strictly limited to testing -> stable, and stable -> oldstable backports, though.<br />
The PGDG apt repository provides a broader range of available packages, and Debian version/PostgreSQL version combinations than what would be possible to provide on backports.<br />
<br />
Similar to the previous FAQ question, if the package you want is available on backports, there is nothing wrong with using it.<br />
<br />
===Are these packages ready for production?===<br />
<br />
As said above, these packages are identical to what Debian/Ubuntu are releasing. So yes, these packages are recommended for production use.<br />
<br />
===What's pgapt.debian.net?===<br />
<br />
pgapt.debian.net was the old location of this repository, and it is still kept in sync for its current users. Please update your sources.list to point to apt.postgresql.org. We will shut down pgapt.debian.net at the end of January 2013.<br />
<br />
==Technical Questions==<br />
<br />
===How do I dist-upgrade?===<br />
<br />
Just update your sources.list entries, and use your favorite package tool.<br />
<br />
When compiling packages for several distributions, we append different suffixes to the package version numbers to make sure, upgrades work. Debian Squeeze packages have ".pgdg60+1" appended, Wheezy packages ".pgdg70+1" and so on; likewise ".pgdg12.4+1" for Ubuntu Precise. (Sid packages have ".pgdg+1", which sorts after all the others in Debian's version number scheme.) This makes sure packages from the old distribution you used will be properly replaced by packages from the new distribution when you do a dist-upgrade.<br />
<br />
===Where is the "stable" distribution?===<br />
<br />
We prefer to only use codenames (squeeze, precise, ...) for distribution names. We run a different repository from Debian, so we would never be able to update the stable/oldstable/testing symlinks at the very same time when a new Debian release comes out, and there would be a time window where users would be seeing an inconsistencies. (Also, it is usually better to use codenames in sources.list even for Debian distributions, because then the user decides when it is time for upgrade.)<br />
<br />
===I want only specific packages from this repository===<br />
<br />
Per default, the PGDG repository will have the same "pinning" priority as your Ubuntu repositories. This means your existing PostgreSQL packages will be replaced with versions from this repository, because they have greater version numbers. If you do not want this, you need to configure pinning (see apt_preferences(5) for details).<br />
<br />
Create the file '''/etc/apt/preferences.d/pgdg.pref''':<br />
<br />
Package: *<br />
Pin: release o=apt.postgresql.org<br />
Pin-Priority: 200<br />
<br />
The default priority for repositories is 500, so 200 will lower the PGDG repository priority enough such you don't get packages automatically installed from there, but once you have packages installed, they will keep getting upgraded to newer versions from the PGDG repository.<br />
<br />
You can check your setup using the '''apt-cache policy''' command to see if "200" shows up in the output:<br />
<br />
$ apt-cache policy postgresql-9.1<br />
postgresql-9.1:<br />
Installed: 9.1.8-1.pgdg70+1<br />
Candidate: 9.1.8-1.pgdg70+1<br />
Version table:<br />
*** 9.1.8-1.pgdg70+1 0<br />
'''200''' <nowiki>http://apt.postgresql.org/pub/repos/apt/</nowiki> wheezy-pgdg/main amd64 Packages<br />
100 /var/lib/dpkg/status<br />
9.1.8-1 0<br />
500 <nowiki>http://ftp.debian.org/debian/</nowiki> wheezy/main amd64 Packages<br />
<br />
<br />
Note about debian wheezy:<br />
The default pinning is higher (990) than the default pinning of additional repositories (eg apt.postgresql.org will get 500) if a target-release is set. <br />
So the behaviour is bit different then with ubuntu. Let's say you want them to have the same priority this time, then u need to put 990 as pinning priority in the file /etc/apt/preferences.d/pgdg.pref. If u don't want to do this, but still have trouble installing try the '''-t wheezy-pgdg''' option with apt or aptitude.<br />
More information about this:<br />
http://www.debian.org/doc/manuals/debian-reference/ch02.en.html#_tweaking_candidate_version<br />
<br />
===I want libpq5 for version X, but there is only version Y in the repository===<br />
<br />
libpq5 is compatible with older versions, so there is usually little reason to use a specific version. (psql requires at least the version corresponding to its own version, most other software does not really care.) For that reason, we ship the libpq5 package built from the latest stable PostgreSQL server version in the ''main'' archive component.<br />
<br />
If you really want to use a different version, the packages are available in separate archive components named after the PostgreSQL major version. Append that version after "main" in your sources.list. For example, if you wanted 9.0's libpq5 on Debian Squeeze, use this:<br />
<br />
deb <nowiki>http://apt.postgresql.org/pub/repos/apt/</nowiki> squeeze-pgdg main '''9.0'''<br />
<br />
Note that the other libpq5 package will still be visible in the "main" component, so you will need to configure pinning or set that package on hold to prevent apt from trying to upgrade to the newer version.<br />
<br />
===I want to try the beta version of the next PostgreSQL release===<br />
<br />
We ship packages for the beta releases, but like in the previous FAQ entry, we have only one version of libpq5 in the ''main'' archive component. To use packages of postgresql-9.3, you need to add the 9.3 component to your <code>/etc/apt/sources.list.d/pgdg.list</code> entry, so the 9.3 version of libpq5 will be available for installation:<br />
<br />
deb <nowiki>http://apt.postgresql.org/pub/repos/apt/</nowiki> wheezy-pgdg main '''9.3'''<br />
<br />
===What are the testing distributions?===<br />
<br />
We have distributions squeeze-pgdg-testing, precise-pgdg-testing, sid-pgdg-testing, ... which receive all updates before they are promoted to the "live" distributions. These are not meant for production use, but you are welcome to use them and help debugging and testing the packages. Please join the mailing list or the IRC channel. The distributions have a lower apt priority by default, you will have to set up pinning (e.g. with priority 500) if you want them to be used automatically.<br />
<br />
===Can I mirror the repository?===<br />
<br />
For a full mirror of some or all distributions, we suggest to use ''debmirror''. For mirroring just a subset of packages, ''reprepro'' offers the ability to "pull" packages from remote repositories.</div>Wibrthttps://wiki.postgresql.org/index.php?title=Audit_trigger&diff=14580Audit trigger2011-06-09T12:46:16Z<p>Wibrt: TG_OP = 'DELETE' returns OLD now instead of NULL</p>
<hr />
<div>{{SnippetInfo|Generic audit trigger function |version=8.4+|lang=PL/pgSQL|category=Library}}<br />
<br />
Here is an example of a generic trigger function used for auditing changes to tables, and optionally updating a "row_last_updated" column with the UTC timestamp of the change. <br />
<br />
The "last updated" timestamp uses "now()", as it is current at the *start* of the transaction, so all actions that occur with each call of the "audit.if_modified_func" will have the same timestamp.<br />
<br />
<source lang="plsql"><br />
-- create a schema named "audit"<br />
create schema audit;<br />
<br />
-- UTC is used as the audit time because if the calling applications are in other time zones, it simplifies things if the source data is in a common time zone.<br />
<br />
create table audit.logged_actions (<br />
schema_name text not null,<br />
table_name text not null,<br />
user_name text,<br />
action_tstamp_utc timestamp not null default (now() at time zone 'UTC'),<br />
action TEXT NOT NULL check (action in ('I','D','U')),<br />
original_data text,<br />
new_data text,<br />
query text<br />
) with (fillfactor=100);<br />
create index logged_actions_schema_table_idx on audit.logged_actions(((schema_name||'.'||table_name)::TEXT));<br />
create index logged_actions_action_tstamp_utc_idx on audit.logged_actions(action_tstamp_utc);<br />
create index logged_actions_action_idx on audit.logged_actions(action);<br />
<br />
<br />
-- generic function for all tables<br />
--drop function if exists audit.if_modified_func() cascade;<br />
CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS trigger AS $body$<br />
DECLARE<br />
v_old_data TEXT;<br />
v_new_data TEXT;<br />
BEGIN<br />
/* If this actually for real auditing (where you need to log EVERY action),<br />
then you would need to use something like dblink or plperl that could log outside the transaction,<br />
regardless of whether the transaction committed or rolled back.<br />
*/<br />
<br />
/* This dance with casting the NEW and OLD values to a ROW is not necessary in pg 9.0+ */<br />
<br />
if (TG_OP = 'UPDATE') then<br />
v_old_data := ROW(OLD.*);<br />
v_new_data := ROW(NEW.*);<br />
insert into audit.logged_actions (schema_name,table_name,user_name,action,original_data,new_data,query) values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data,v_new_data, current_query());<br />
-- Alternative version, just log the query, not the contents of the columns<br />
--insert into audit.logged_actions (schema_name,table_name,user_name,action,query) values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),current_query());<br />
<br />
-- to update the row_last_updated column if implemented<br />
NEW.row_last_updated = timezone('UTC'::text, now());<br />
RETURN NEW;<br />
elsif (TG_OP = 'DELETE') then<br />
v_old_data := ROW(OLD.*);<br />
insert into audit.logged_actions (schema_name,table_name,user_name,action,original_data,query) values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data, current_query());<br />
RETURN OLD;<br />
elsif (TG_OP = 'INSERT') then<br />
v_new_data := ROW(NEW.*);<br />
insert into audit.logged_actions (schema_name,table_name,user_name,action,new_data,query) values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data, current_query());<br />
-- no need to set the "row_last_updated" timestamp, as that is handled using the "DEFAULT" option on in the table DDL<br />
RETURN NEW;<br />
else<br />
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %, at %',TG_OP,now();<br />
RETURN NULL;<br />
end if;<br />
<br />
EXCEPTION<br />
WHEN data_exception THEN<br />
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;<br />
RETURN NULL;<br />
WHEN unique_violation THEN<br />
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;<br />
RETURN NULL;<br />
WHEN others THEN<br />
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;<br />
RETURN NULL;<br />
END;<br />
$body$ language plpgsql security definer;<br />
</source><br />
<br />
<br />
-- Tested with a table named "t"<br />
drop table if exists t;<br />
create table t (x int not null primary key, y text);<br />
alter table t add row_last_updated timestamp not null default (now() at time zone 'UTC');<br />
<br />
-- needs to be applied to all tables that we want to monitor<br />
<br />
-- this is a test trigger to show how we can audit all changes to the relevant tables, including inserts<br />
create trigger t_if_modified_trg before insert or update or delete on t for each row execute procedure audit.if_modified_func();<br />
<br />
<br />
-- Some sample updates, deletes, and inserts to illustrate the points<br />
select * from t; select * from audit.logged_actions;<br />
<br />
insert into t (x,y) values (1,'asdf'),(2,'werwer'),(3,null);<br />
select * from t; select * from audit.logged_actions;<br />
<br />
update t set y='eeeeee' where x=2;<br />
select * from t; select * from audit.logged_actions;<br />
<br />
update t set y='yuyuyuy' where x=3;<br />
select * from t; select * from audit.logged_actions;<br />
<br />
delete from t where x=1;<br />
select * from t; select * from audit.logged_actions;<br />
<br />
-- should be a pk violation<br />
update t set x=4 where x=2;<br />
select * from t; select * from audit.logged_actions;<br />
<br />
<br />
''Original code by bricklen. Any errors are mine''<br />
<br />
[[Category:PL/pgSQL]]</div>Wibrt