Serial Postgresql Primary Key

Note: The following is part of a series of steps to setup an email server using Exim 4.x, with imap and webmail access. It will use winbind to get user information from an NT server.

If you found this page via a search engine it may not cover what you need or you may need to to understand everything I have done. (Note: These instructions reference software that is now possibly much newer with many new or different configuration options. This page is being left up for reference.) Quick Steps: • • • • • • • • • • • • • • For PostgreSQL I'm using version 7.3.4. Note: Databases are kept in /var/lib/pgsql Documentation can be found here: Some basic SQL: Another RedHat how-to: 1) Download and Install PostgreSQL I used the RPMs from. 2) Inital setup and config files First initalize the database (only need to do this once): su -m postgres initdb -D /var/lib/pgsql/data exit There are now some files in /var/lib/pgsql/data that can be edited.

May 20, 2015. As big users of PostgreSQL, we had the opportunity of re-thinking the idioms common in the world of relational DBs. Today, I'll talk about why we stopped using serial integers for our primary keys, and why we're now extensively using Universally Unique IDs (or UUIDs) almost everywhere.

Pg_hba.conf is used to specify client connection and authentication rules. Here are some examples. #Type Database User IP-Address IP-Mask Method # allow local user with password local all all password # allow local user without password local all all trust # allow (private ip range) with password host all all 192.168.1.0 255.255.255.0 password # allow localhost, connecting via tcp/ip, without password host all all 127.0.0.1 255.255.255.255 trust pg_ident.conf is used to map ident/Unix usernames to PostgreSQL usernames. Postgresql.conf is for run-time options. This may be of help:.

Here is what I have changed in mine. # Connection Parameters # tcpip_socket = true #ssl = false max_connections = 27 superuser_reserved_connections = 2 port = 5432 hostname_lookup = false #show_source_port = false 3) Start PostgreSQL service postgresql start To run at startup: chkconfig postgresql on 4) Create a superuser for PostgreSQL Postgresql has one superuser defined already, which is typically called postgres and is the user name PostgreSQL runs under. If you need to do 'superuser' things and are logged in as root, you can: su -l postgres To make a superuser with a password, as root do: su -l postgres createuser -a -P user exit You will be prompted for a password, or you can leave the '-P' option off for none. Use: createuser --help for more options.

5) Set up the User(s) For the LDAP backend a user is required. For security, I think it is best not to have this user be a superuser. Users can be created using the createuser, as shown above, or with the SQL command: psql -d database_name -h host_name -U username -W CREATE USER username WITH PASSWORD ' password'; If you need a database to connect to, try 'template1'. See the for more.

Also see 'pg_ident.conf' for how to map *nix-to-PostgreSQL names and 'pg_hba.conf' for connection restrictions. Example user setup: User dbedit, with password, can access from all hosts and has the privileges of Select, Insert, Update, Delete, Rule, References, and Trigger. This is the user I will use when making an ODBC connection from MS Access. User ldapac, with no password, can access from localhost and has the privilege of Select, because I am not editing with LDAP at this time. Note that privileges are set per table. GRANT ALL ON TABLE table_name TO user; GRANT SELECT ON TABLE table_name TO user; 6) Set up the Database(s) See the or use something like. (Note: I've found Webmin support for managing PostgreSQL lacking compared to it's support for MySQL.

Maybe if I ever find time I'll help fix that, but until then be warned.) There is a Windows version of psql. Quick psql notes: To connect to a database via TCP/IP with a username and password use: psql -d database_name -h host_name -U username -W To connect to a database via TCP/IP, no password, and execute SQL from a file: psql -d database_name -h host_name -U username.

CREATE VIEW email_domains (domain ) AS SELECT LOWER (Substr (emailaddress,Strpos (emailaddress, '@' ) +1, ( LENGTH (emailaddress )-Strpos (emailaddress, '@' ) ) ) ) AS X FROM tblcontacts GROUP BY LOWER (Substr (emailaddress,Strpos (emailaddress, '@' ) +1, ( LENGTH (emailaddress )-Strpos (emailaddress, '@' ) ) ) ); If you need more information about the back-sql setup, go and read 'Simple Example Setup'. Notes about creating ldap_entries as a VIEW can be found there. 7) Backing up the database Refer to the documentation,, for more information.

How to backup without shutting down the database by specifing the host, database name, a user to connect as, to be prompted for a password, and to compress the output: pg_dump -h localhost -d database_name -U username -W gzip >file_name For iODBC The following is needed for back-sql. If you are only trying to interface MS Access with PostgreSQL you can skip this section. 1) Get iODBC Driver Manager Available in an RPM.

IODBC 2. Usb Piano Keyboard Programs. 50.3 or later needed & the devel-*.rpm as well. Install with: rpm -ivh name_of.rpm You may also want the SDK which has the odbctest program. The link is at the bottom of the page for downloading the binaries.

Note: Its extension is.taz. Just gunzip then tar -xvf to unpack. C5 Projector Headlights Install here. 2) Install psqlODBC Download and compile the source.

I'm using 7.2.5: Take the time to read the documentation available there. You'll also need the (pre-compiled) Windows one if you plan on using the database with something like MS Access. How to manually compile (for iodbc): tar xvzf psqlodbc- [version].tar.gz cd psqlodbc- [version]./configure --with-iodbc --enable-pthreads make make install See the README.unix file for more information. How to make an rpm of the source: • Place psqlodbc- [version].tar.gz in /usr/src/redhat/SOURCES • Create psqlodbc.spec in /usr/src/redhat/SPECS • Run rpmbuild -bi psqlodbc.spec to test compiling. • Run rpmbuild -bb psqlodbc.spec to build the binaries or psqlodbc -ba exim.spec to build all.

Note: I am running rpm 4.1.1-1.8x. See for the latest.

To download my rpm or view my full spec file,. 3) Configure iODBC on *nix Edit the /etc/odbc.ini file. Here is a sample one using psqlodbc 7.2.5. '*************************************************************** 'The DoesTblExist function validates the existence of a TableDef 'object in the current database. The result determines if an 'object should be appended or its Connect property refreshed.

'*************************************************************** Function DoesTblExist(strTblName As String) As Boolean On Error Resume Next Dim db As Database, tbl As TableDef Set db = CurrentDb Set tbl = db.TableDefs(strTblName) If Err.Number = 3265 Then ' Item not found.